Summing Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Summing Fields

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-15 : 06:57:07

I have a query :

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid , week31salesquantity, week32salesquantity, week33salesquantity, week34salesquantity, week35salesquantity, week36salesquantity, week37salesquantity, week38salesquantity, week39salesquantity, week40salesquantity, week41salesquantity, week42salesquantity, week43salesquantity, week44salesquantity
from sanitaUnitCost s inner join lrho1 l on
(s.EAN = l.eannumber)
inner join bell b on
(l.eannumber = b.barcode)
WHERE l.instorejersey = 'Y'
And isnumeric(s.unitprice) = 1
And convert(numeric(18,2), l.JerseyUnitPrice) <> convert(numeric(18,2), s.UnitPrice)

that has 15 weekly sales quantity values.
I know this might sound a stupid question but how would I get all these weekly quantity values into one accumlitive amount field through writing one query , instead of outputting to excel and adding each items weekly qunatity values up manually.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-15 : 07:05:27

is this what you want ?

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid , week31salesquantity + week32salesquantity + week33salesquantity + ...  week44salesquantity

-----------------
[KH]
Learn something new everyday

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-15 : 07:05:38

1....ouch with the design!!!.....please shoot that designer....this headache will be the 1st of many!!!

2.

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid , week31salesquantity, week32salesquantity, week33salesquantity, week34salesquantity, week35salesquantity, week36salesquantity, week37salesquantity, week38salesquantity, week39salesquantity, week40salesquantity, week41salesquantity, week42salesquantity, week43salesquantity, week44salesquantity, week31salesquantity + week32salesquantity + week33salesquantity + week34salesquantity + week35salesquantity + week36salesquantity + week37salesquantity + week38salesquantity + week39salesquantity + week40salesquantity + week41salesquantity + week42salesquantity + week43salesquantity + week44salesquantity
from sanitaUnitCost s inner join lrho1 l on
(s.EAN = l.eannumber)
inner join bell b on
(l.eannumber = b.barcode)
WHERE l.instorejersey = 'Y'
And isnumeric(s.unitprice) = 1
And convert(numeric(18,2), l.JerseyUnitPrice) <> convert(numeric(18,2), s.UnitPrice)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-15 : 07:06:34

By adding them up manually in SQL

SELECT l.eannumber, l.prodcode, l.[description] ,l.jerseysupplierid ,
week31salesquantity + week32salesquantity + week33salesquantity + week34salesquantity + week35salesquantity + week36salesquantity +
week37salesquantity + week38salesquantity + week39salesquantity + week40salesquantity + week41salesquantity + week42salesquantity + week43salesquantity + week44salesquantity AccumSales
from sanitaUnitCost s inner join lrho1 l on
(s.EAN = l.eannumber)
inner join bell b on
(l.eannumber = b.barcode)
WHERE l.instorejersey = 'Y'
And isnumeric(s.unitprice) = 1
And convert(numeric(18,2), l.JerseyUnitPrice) <> convert(numeric(18,2), s.UnitPrice)

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-15 : 07:08:40

There you go Jim - 3 Very quick responses with the same solution

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-15 : 07:14:42

what more can you ask for ?

-----------------
[KH]
Learn something new everyday

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-15 : 07:24:05

Thanks Guys but I tried this and I was just getting these values concatenated Which is not what I require I require the sum of all these values in one field ?
Ps I know the design is shocking.... Codd would be rolling in his grave !

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-15 : 07:27:00

mmmmm... if they are concantenated then it means that they are char or varchar fields - so you will have to cast each field to a numeric data type before adding them
eg cast(week32salesquantity as numeric(18, 2)) + cast(week33salesquantity as numeric(18, 2)), ..... etc

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-15 : 07:32:33

Ah that is the issue I have once again overlooked .Have tried that Duanne and it works a treat.Thank you once again.
Just to put this to rest I used a cursor to extract all the information into these tables from a large text file, that is why everything is so denormalized...but on the other hand this query is only used for reporting..If that is a good enough excuse for the the poor design ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-15 : 09:24:23

aaargh...."cursor".....beware the demons!!!! You really are a Joe seeking a beating.
search here for 'cursor', 'performance' and 'NOT, P*SS POOR or CR*P' and maybe you'll end the day learning something career changing.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-15 : 10:08:23

You are abit of a tool I think Andrew, Nobody likes a smart ass, you w@nker....

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-16 : 06:28:17

You're a bigger fool yourself Jim if you don't take the advice and seek out the problems of working with cursors in SQL. It might have been said in a tongue-in-cheek style, but the underlying message is extremely valuable for anybody looking to get the full benefits of SQL Server.

Remember the rules of online fora as well....tread softly, softly until you establish the groundrules/etiquette in place when a newbie....otherwise the next time you post, you get might SFA responses!! Nobody likes a brash newcomer either, who hasn't the smarts to understand the mechanics or and personalities behind the 'free advice' forum he's just joined!

Welcome!!

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-16 : 09:26:47

Ok I apologise Andrew, but I think you started the harsh comments in the first place so I just assumed this was common practice when addressing yourself.But its water under the bridge so I apologise for upsetting you.

Back to the cursor issue I cannot find any other way of extracting specific information out of a text document where the likes of this :

PA 06147906 25 15 170020 500024100118200000000000000walls sausages 200 g 1L 0 0000000000003N00NN 0000A0145122147887000000N LARGNY NN000000NNBS 00000000CRISP'N'DRY 1L Y S000000000 00242970 00N 001
RA 031479065000241001182010199990 110010J T001 000016000001410000000014100000088YN
RA 031479065000241001182020299995 110010G A076 000016000001410000000014100000088YN
RA 031479065000241001182030321111 97470JS 000016000001485000000014850000000YN
RA 031479065000241001182040421111 97470GS 000016000001485000000014850000000YN
QA 02147906500024100118200010301N@000000Y001LARG
QA 02147906500024100118200010401N@000000Y001LARG
QA 02147906500024100118200010901N@000000Y001LARG
QA 02147906500024100118200011001N@000000Y001LARG
QA 02147906500024100118200011801N0000000N
QA 02147906500024100118200018001N@000000Y001LARG
QA 02147906500024100118200018101N@000000Y@@@@@@@
QA 02147906500024100118200018401N0000000N
QA 02147906500024100118200018601N@000000Y001LARG
QA 02147906500024100118200018901N@000000Y@@@@@@@
QA 02147906500024100118200020502N0000000N
QA 02147906500024100118200050001N 000000Y
QA 02147906500024100118200060001N 000000Y
QA 021479065000241001182 AJY103Y0000000N000LARG
QA 021479065000241001182 AJY201Y0000005N000LARG
QA 021479065000241001182 AJY301Y0000005N000LARG
QA 021479065000241001182 AJY401Y0000005N000LARG
QA 021479065000241001182 AJY501Y0000005N000LARG
QA 021479065000241001182 BGY104Y0000005N000LARG
QA 021479065000241001182 BGY202Y0000005N000LARG
QA 021479065000241001182 BGY302Y0000005N000LARG
QA 021479065000241001182 BGY402Y0000005N000LARG
QA 021479065000241001182 BGY502Y0000005N000LARG

is only just one record of a 80000 record file .
What would you suggest then Andrew ?

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-16 : 09:40:45

A cursor is a very slow way to executing SQL code....as robvolk put it a long time ago..it's a bit like filling a teacup with sugar using a tweezers to pick each grain up 1 by 1, instead of using a spoon.

declaring a cursor like
'select * from table1"
and then looping through it like
while @@fetch_status = 0
insert into tableb (fields from table1)
get next cursor record
loop

can be achieved far faster by just doing

insert into table2
select * from table1

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-16 : 10:22:07

I do appreacitte the advice thanks Andrew I just dont reckon this technique will work for my circumsatance where I need to extract specific items form a narrow table that is sometimes a varchar(3000) and sometimes a string of 2 characters needs to be extracted for one line ...

But I might be mistaken....
Bottom line is my cursor has worked and took 1 minute to execute and extract all relevant information....
Maybe if I used a select into statement it would take 50 seconds.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-16 : 10:24:21

all the best for the festive season by the way Andrew and all the rest of you guys.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-19 : 06:09:37

And many happy returns too Jim.....keep a lid on the festive pud or it'll put a lid on you.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2005-12-19 : 07:20:47

will do my best but I can't promise anything.