question

sqlnewb avatar image
sqlnewb asked

Combine two queries

I am trying to write a query that counts the number of specific code appear for each id and each country. In the query I want two counts. 1st I want all the codes that are equal to 1 and the other count all the codes that are not equal to 1. I have two seperate queries that accomplish this but I want it in one query and am having problem getting it combined. select ID, Country, COUNT(code) as CntNON_1 from tableA where Code <> '1' group by UltimateID, Country select ID, Country, COUNT(icode) as Cntof1 from tableA where Code = '1' group by UltimateID, Country order by Country I've tried to join them on country and id but didn't get the proper result id country CntNon_1 Cnt1 1 US 3 1 1 China 2 7 etc
sql-server-2008t-sqlcount
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I liked the way you framed the question. It was nice and clear. We need to vote questions like this up more.
1 Like 1 ·
It's good that @sqlnewb has had a go at the problem as well rather than just throw the whole thing over to us...
0 Likes 0 ·

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
Something like this might do the job: SELECT ID, Country, SUM (CASE WHEN Code = '1' THEN 1 ELSE 0 END) AS Cntof1, SUM (CASE WHEN Code 1 THEN 1 ELSE 0 END) AS CntNON_1 FROM TableA GROUP BY UltimateID, Country
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.