SQL Counting trouble

Question

I have the following scenario:

I need to count how many clients have a status of unpaid. and this count must only show those which have a count >= 2. This part I am doing correctly (I think):

--SELECTING Client Count
SELECT Client, COUNT(HyphenStatus) as UnpaidCount
FROM CasesFromSimplicity
WHERE HyphenStatus = 'UnPaid' AND FCManager = 'Mike'
GROUP BY Client
HAVING COUNT(HyphenStatus) >= 2
ORDER BY Client ASC

I then need to create a table which looks like this for the manager view:

+-----------+--------------+----------------+------------+
| FCManager | CountingTwos | CountingThrees | FourOrMore |
+-----------+--------------+----------------+------------+
| Mike      |            2 |              1 |          1 |
+-----------+--------------+----------------+------------+

This tells me that My consultant Mike has two clients with a count of 2 and one client with a count of 3 and one client with a count of 4.

I have made the following query at the moment, but this doesn't seem to be working.

--Selecting the count for manager
SELECT
FCManager
,(SELECT COUNT(HyphenStatus) FROM CasesFromSimplicity WHERE HyphenStatus = 'UnPaid' AND FCManager = 'Mike' HAVING COUNT(HyphenStatus) = 2) CountingTwos
,(SELECT COUNT(HyphenStatus) FROM CasesFromSimplicity WHERE HyphenStatus = 'UnPaid' AND FCManager = 'Mike' HAVING COUNT(HyphenStatus) = 3) CountingThrees
,(SELECT COUNT(HyphenStatus) FROM CasesFromSimplicity WHERE HyphenStatus = 'UnPaid' AND FCManager = 'Mike' HAVING COUNT(HyphenStatus) >= 4) FourOrMore
FROM CasesFromSimplicity
GROUP BY FCManager

Could anyone please tell me what I am doing wrong? Or send me to the right place to read on this. I couldnt find anything while searching. I have been thinking while posting that I will need a group by in the sub queries, but this would then return more than 1 result and not work?

I have created a SQL Fiddle for you to see my issue Live.

Any help would be appreciated, thanks Mike.

Please let me know if I need to explain this further? I am using SQL SERVER 2008

Lamak · Accepted Answer · 2014-05-08 14:41:02Z

2

Well, assuming SQL Server 2005+, you can do:

;WITH CTE AS
(
    SELECT  FCManager,
            Client, 
            COUNT(HyphenStatus) as UnpaidCount
    FROM CasesFromSimplicity
    WHERE HyphenStatus = 'UnPaid' 
    GROUP BY FCManager,
             Client
    HAVING COUNT(HyphenStatus) >= 2
)
SELECT  FCManager,
        SUM(CASE WHEN UnpaidCount = 2 THEN 1 ELSE 0 END) CountingTwos,
        SUM(CASE WHEN UnpaidCount = 3 THEN 1 ELSE 0 END) CountingThrees,
        SUM(CASE WHEN UnpaidCount > 3 THEN 1 ELSE 0 END) FourOrMore 
FROM CTE 
GROUP BY FCManager

Here is the modified sqlfiddle.

answered May 8, 2014 at 14:41

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fizor Over a year ago

That is awesome! :) Thank you so much. I will have to go read up on this WITH CTE you speak of because I have no clue what that does.

Lamak Over a year ago

@Mike The CTE (common table expression) in this case is helping with readability only. You could've use a derived table as well (like, instead of WITH CTE..., just do SELECT FCManager..... FROM (the same query that I used in the CTE) .....)

Collectives™ on Stack Overflow

SQL Counting trouble

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related