CODEWITHSUNDEEP
sql-servert-sqlselect
Mario Vega
Mario Vega

Reputation: 67

Get jobs count without using group by

I have the following code to calculate the number of jobs of a certain client.

SELECT CLIENT_ID,
COUNT(CASE WHEN CURRENT= 'Y' then 1 end) as JOBS_COUNT
FROM CLIENTS
GROUP BY CLIENT_ID

Nevertheless, to complete this I need to use a group by at the end, what I'm looking for is a query that counts the flags CURRENT, within a partition, so I do no have to use a group by at the end, is that possible?

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

I think you want:

SELECT CLIENT_ID,
       COUNT(CASE WHEN CURRENT = 'Y' then 1 end) OVER (PARTITION BY CLIENT_ID) as JOBS_COUNT
FROM CLIENTS;

This seems like an odd request, because you return multiple rows per CLIENT_ID. However, that appears to be what you are asking for.

Upvotes: 1

Gudwlk
Gudwlk

Reputation: 1157

You can give a row_number() over the partition and select rn =1 to avoid duplicates over the partition using a derived table as well. The solution I added gets distinct partitions with count. If you need more help, let me know.

SELECT Distinct CLIENT_ID,
   COUNT(CASE WHEN CURRENT= 'Y' then 1 end) OVER (PARTITION BY ep.CLIENT_ID) as 
 jos_count as JOBS_COUNT
FROM CLIENTS

Upvotes: 1

Related Questions