CODEWITHSUNDEEP
sqlsql-servercountgroup-bysubquery
Dean Flaherty
Dean Flaherty

Reputation: 351

sql server - group by in subquery

I am having trouble grouping my results in a subquery. Currently the results I am getting just give the total of all the standard_crqs next to each crq_requested_by_company and don't group it by crq_requested_by_company.

I have the following code 

SELECT crq_requested_by_company 
       ,COUNT(crq_number) as 'count crqs'
       ,(SELECT count(crq_number) FROM change_information where crq_change_timing = 'Standard') as 'standard crqs' 
FROM change_information
GROUP BY crq_requested_by_company

My results look like this. The standard_crqs should all be less than the overall count for each crq_requested_by_company

crq_requested_by_company    count   standard_crqs   standard_crqs(correct)
A                             4          2824             3
B                             2          2824             1
C                             2269       2824             1745 
D                             7696       2824             3456
E                             110        2824             56
F                             91         2824             17
G                             33         2824             23
H                              295       2824             78

If I just query 

SELECT count(crq_number)
FROM change_information 
WHERE crq_change_timing = 'Standard' 
GROUP BY crq_requested_by_company

this works perfectly fine

Upvotes: 0

Views: 95

Answers (3)

Dani Mathew
Dani Mathew

Reputation: 828

Your SQL query needs a 'CASE' to be put in to filter 'crq_change_timing' against 'standard'

See the correction I have made,

SELECT crq_requested_by_company 
   ,COUNT(crq_number) as 'count crqs'       
   ,(SELECT count(crq_number) FROM change_information 
            where crq_change_timing = 'Standard') as 'standard crqs' 
   ,COUNT(CASE WHEN crq_change_timing = 'Standard' THEN 1 END)as 'standard crqs (Correct)'
FROM change_information
GROUP BY crq_requested_by_company

Upvotes: 6

Zigri2612
Zigri2612

Reputation: 2310

You can use Case Instead SubQuery -- 

SELECT crq_requested_by_company 
       ,COUNT(crq_number) as 'count crqs'
       ,COUNT(CASE WHEN crq_change_timing = 'Standard' THEN 1 END)as 'standard crqs' 
FROM change_information
GROUP BY crq_requested_by_company

Because you are getting information from same table.so its a best solution.

Upvotes: 0

Conffusion
Conffusion

Reputation: 4465

SELECT crq_requested_by_company 
   ,COUNT(crq_number) as 'count crqs'
   ,(SELECT count(crq_number)
      fROM change_information ci2 
      where crq_change_timing = 'Standard'
        and ci2.crq_requested_by_company = ci1.crq_requested_by_company)     
as 'standard crqs' 
FROM change_information c1
GROUP BY crq_requested_by_company

Upvotes: 2

Related Questions