Saja
Saja

Reputation: 1

How do I use SQL count with group by causing an error

Here is my code

SELECT 
(SELECT count(*) WHERE status_id = @TO) as 'TO' ,
(SELECT count(*) WHERE status_id = @RO)as 'RO' ,
(SELECT count(*) WHERE status_id = @AO)as 'AO',
(SELECT count(*) WHERE status_id = @IO)as 'IP',
(SELECT count(*) WHERE status_id = @SO)as 'SO',
          created_user
      FROM
          table1
      GROUP BY
          created_user

Now i'm getting the following error

Column 'status_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't want to group by status_id, how to fix this issue?

Upvotes: 0

Views: 110

Answers (1)

Sergey
Sergey

Reputation: 5217

I guess you can try to use conditional aggregation

 SELECT created_user,
  SUM
  (
     CASE
        WHEN status_id = @TO THEN 1 ELSE 0 END
  )AS 'TO',
 SUM
  (
    CASE
      WHEN status_id = @RO THEN 1 ELSE 0 END
 )AS 'RO'
FROM table1
  GROUP BY created_user

Upvotes: 1

Related Questions