0 votes
1 view
in SQL by (20.3k points)

I am wondering how to write this query.

I know this actual syntax is bogus, but it will help you understand what I am wanting. I need it in this format, because it is part of a much bigger query.

SELECT distributor_id, 

COUNT(*) AS TOTAL, 

COUNT(*) WHERE level = 'exec', 

COUNT(*) WHERE level = 'personal'

I need this all returned in one query.

Also, it need to be in one row, so the following won't work:

'SELECT distributor_id, COUNT(*)

GROUP BY distributor_id'

1 Answer

0 votes
by (40.3k points)

You can use aggregate function with the CASE Statement. As it’s similar to PIVOT function in RDBMS:

You can refer to this video to learn CASE STATEMENT in SQL server:

select distributor_id,

count(*) total,

sum(case when level = 'exec' then 1 else 0 end) ExecCount,

sum(case when level = 'personal' then 1 else 0 end) PersonalCount

from yourtable

group by distributor_id

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
+3 votes
1 answer
asked Jul 3, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...