Combine rows based on 2 common fields

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

General SQL Server Forums

New to SQL Server Programming

Combine rows based on 2 common fields

Author

Topic

klondono
Starting Member

6 Posts

Posted - 2011-07-11 : 10:21:18

The following query extracts the total inspections done for a particular Shop and License number combination from the InspectionTable. I need to see for a particular shop, how many inspections were done by each officer and the total nuymber of inspections. However, the query results in multiple rows for the same shop instead of combining the results as one record per shop. Below is the query and the resulting table.

select
[Shop] = T.ShopName
, [LicenseNo] = T.LicenseNo
, [# Insp] = count(*)
, [Alfredo] = (SUM(case when T.Officer = 'Alfredo Diaz' then 1 else 0 end))
, [Keith] = SUM(case when T.Officer = 'Keith Bedasie' then 1 else 0 end)
, [Pedro] = SUM(case when T.Officer = 'Pedro Carvajal' then 1 else 0 end)
, [Max] = SUM(case when T.Officer = 'Max Freiria' then 1 else 0 end)
, [John] = SUM(case when T.Officer = 'John Williams' then 1 else 0 end)
, [Victor] = SUM(case when T.Officer = 'Victor Baella' then 1 else 0 end)
from InspectionTable T
group by T.ShopName, T.LicenseNo, T.Officer
ORDER BY [ShopName] ASC

Shop LicenseNo Alfredo Keith Pedro Max John Victor # Insp
ABC 123 2 0 0 0 0 0 2
ABC 123 0 0 0 2 0 0 2
ABC 123 0 0 1 0 0 0 1
I need it to look like this where multiple rows are combined into one based on the Shop and LicenseNo columns:

Shop LicenseNo Alfredo Keith Pedro Max John Victor # Insp
ABC 123 2 0 1 2 0 0 5

Any help will be greatly appreciated. Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-11 : 10:53:11

Not sure if I get you right but try to remove T.OFFICER from the GROUP BY clause...

No, you're never too old to Yak'n'Roll if you're too young to die.

X002548
Not Just a Number

15586 Posts

Posted - 2011-07-11 : 10:57:33

[code]

SELECT Shop, LicenseNo, SUM(Alfredo), SUM(Keith), SUM(Pedro), SUM(John), SUM(Victor), SUM([# Insp])
SELECT
[Shop] = T.ShopName
, [LicenseNo] = T.LicenseNo
, [# Insp] = count(*)
, [Alfredo] = SUM(case when T.Officer = 'Alfredo Diaz' then 1 else 0 end)
, [Keith] = SUM(case when T.Officer = 'Keith Bedasie' then 1 else 0 end)
, [Pedro] = SUM(case when T.Officer = 'Pedro Carvajal' then 1 else 0 end)
, [Max] = SUM(case when T.Officer = 'Max Freiria' then 1 else 0 end)
, [John] = SUM(case when T.Officer = 'John Williams' then 1 else 0 end)
, [Victor] = SUM(case when T.Officer = 'Victor Baella' then 1 else 0 end)
FROM InspectionTable T
GROUP BY T.ShopName, T.LicenseNo, T.Officer
ORDER BY [ShopName] ASC
) AS XXX
GROUP BY T.ShopName, T.LicenseNo

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-07-11 : 10:59:19

quote:
Originally posted by webfred

Not sure if I get you right but try to remove T.OFFICER from the GROUP BY clause...

No, you're never too old to Yak'n'Roll if you're too young to die.

Or that.

Post some sample DML

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

klondono
Starting Member

6 Posts

Posted - 2011-07-11 : 11:38:50

Thank you so much!!! I removed the group by T.Officer & it worked perfectly. Thanks again!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources