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
I liked the way you framed the question. It was nice and clear. We need to vote questions like this up more.
It's good that @sqlnewb has had a go at the problem as well rather than just throw the whole thing over to us...
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
