help with query - 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)

help with query

Author

Topic

forwheeler
Starting Member

44 Posts

Posted - 2007-05-21 : 11:58:57

Here is what I get from my query:

last, first, desc, code, amount
mouse mickey SALARY 4000 5000.00
mouse mickey GROUP INSURANCE - LIFE 4201 42.16
mouse mickey GROUP INSURANCE - HLTH & ACCID 4202 2000.25
mouse mickey WORKER'S COMPENSATION 4203 104.19

Every code that starts with 42 I want to total. The other codes I just want to display. Here is what I want:
mouse mickey 4000 5000.00
mouse mickey 4200 2146.60

Here is my query:

SELECT d.last_name,d.first_name, b.object_desc, code,

SUM(a.amount)
FROM payroll a,
bud_object b,
employee_detail c,
employee_master d,
transaction_master e
WHERE a.pca = @pca
AND e.Effective_date = '2007'

AND a.expense_so= b.object_code
AND c.ssn=d.ssn
AND a.ssn=c.ssn
AND a.pcn=c.pcn
AND a.payroll_no=c.payroll_no
AND a.transaction_id=e.transaction_id
AND a.pay_date=c.pay_date
AND substring(code,1,2)='42'

GROUP BY d.last_name,d.first_name,b.object_desc,code

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 12:01:19

SUM(CASE WHEN code LIKE '42%' THEN amount ELSE 0 END) AS MyTotal

forwheeler
Starting Member

44 Posts

Posted - 2007-05-21 : 14:16:25

Thanks for your response Kristen.
I get the same result. The only difference is that the amount is 0 if the code is not a '42' code.
Any other ideas?

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 14:48:51

"I get the same result"

Is it the WRONG result?

You had

AND substring(code,1,2)='42'

in the original query, which is presumably ONLY including the relevant values for CODE beginning with "42". If I've understood you correctly you will need to remove that once you put my suggested SUM() in place.

Kristen

forwheeler
Starting Member

44 Posts

Posted - 2007-05-21 : 15:05:45

I get:

last, first, desc, code, amount
mouse mickey SALARY 4000 0
mouse mickey GROUP INSURANCE - LIFE 4201 42.16
mouse mickey GROUP INSURANCE - HLTH & ACCID 4202 2000.25
mouse mickey WORKER'S COMPENSATION 4203 104.19

Which is what I had before. I want the sum of all the 42 codes rolled up to one row as in my example.

mouse mickey 4000 5000.00
mouse mickey 4200 2146.60

Kristen
Test

22859 Posts

Posted - 2007-05-21 : 15:11:52

Sounds like you want the answer to a different question to me!

Is this what you want?

1) Take the first two digits of the code; Append "00" i.e. 4000-> "40"+"00", 4001-> "40"+"00", 4201-> "42"+"00" ...

Show the total where CODE starts with those 2 digits.

??

Kristen

forwheeler
Starting Member

44 Posts

Posted - 2007-05-21 : 18:36:44

No, I do need the code to display 4200 but the real issue is that I want the all the codes that have the first two digits of 42 to have their amounts summed all together on one row.

mouse mickey 4200 2146.60

Thanks

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 02:05:12

"I do need the code to display 4200"

Sorry, I must be missing something. There isn't a data value with Code = '4200' in your data.

There are, however, three rows with codes '4201', '4202' and '4203' and their amounts total 2146.60.

That suggests to me that you want to aggregate on the first two digits and then append "00" to them. (Or if Code is numeric you could Round it to the nearest Hundred).

[Which was the process I thought I described!]

Kristen

forwheeler
Starting Member

44 Posts

Posted - 2007-05-22 : 08:49:25

I can handle the renaming of the code. I need help with the summing of the amount field such as

select SUM(amount)from tables where substring(code,1,2)='42'

I want this to diplay in one row-the sum for all amounts for 42 codes.

Thanks

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 12:08:47

select SUM(amount)from tables where substring(code,1,2)='42'

is going to give you 2146.60, isn't it? - if that isn't what you want can you give me an example of what you want the output to look like.

(Sorry, I still don't get it!)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources