% calculation in query with join and group by clause

Question

I have a query which counts pickups, and divides it by client ethnicity and category. It works, but I need calculate one column 'Pickups as % of Active' which is total count of active clients in client table (Active client is StatusID in (1,2,6) in Clients table, all other statuses count as nonactive) My query looks like

SELECT
max(a.AgencyName)as Agency,
COUNT(PickupID)as Pickups,
( COUNT(PickupID)/(select  COUNT( c.ClientID) AS ActiveClients 
    FROM Clients c
    WHERE c.StatusID in (1,2,6) 
    GROUP BY c.AgencyID)) as 'Pickups as % of Active',
--SUM(CASE WHEN c.StatusID in (1,2,6) THEN 1 ELSE 0 END)/ COUNT(PickupID) as 'Pickups as % of Active',
count (CASE WHEN p.CategCode = 'NB' THEN p.CategCode END) as NB
,count (CASE WHEN p.CategCode = 'IN' THEN p.CategCode END) as 'IN'
,count (CASE WHEN p.CategCode = 'CH' THEN p.CategCode END) as 'CH'
,count (CASE WHEN p.CategCode = 'PG' THEN p.CategCode END) as 'PG'
,count (CASE WHEN p.CategCode = 'BF' THEN p.CategCode END) as 'BF'
,count (CASE WHEN p.CategCode = 'PP' THEN p.CategCode END) as 'PP'
,count (CASE WHEN p.CategCode = 'SR' THEN p.CategCode END) as 'SR'

,count (CASE WHEN p.EthnCode = 'N' THEN p.EthnCode END) as Nat
,count (CASE WHEN p.EthnCode = 'A' THEN p.EthnCode END) as Asn
,count (CASE WHEN p.EthnCode = 'B' THEN p.EthnCode END) as Blk
,count (CASE WHEN p.EthnCode = 'P' THEN p.EthnCode END) as Pac
,count (CASE WHEN p.EthnCode = 'W' THEN p.EthnCode END) as Wth
,count (CASE WHEN p.EthnCode NOT IN ('N', 'A', 'B', 'P', 'W', '0') THEN p.EthnCode END) as Nat
,count (CASE WHEN p.EthnCode = '0' THEN p.EthnCode END) as Unknown
,count (CASE when PickupHispanic=1 then PickupHispanic end) as Hispanic 

FROM dbo.Pickup p join Agency a on p.agencyid = a.agencyid join Clients c ON c.ClientID = p.ClientID
where  PickupDate  between '2013-01-01' and '2013-01-31' 
group by a.AgencyID 

WITH ROLLUP
order by 1

end it throws error

Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

Neil Mussett · Answer 1 · 2013-03-14T01:37:14.883

Add your test to an expression that returns 1 if successful and 0 otherwise. Then add it up and divide it by your total to get the percentage.

You can use something like the following:

SUM(CASE WHEN c.StatusID in (1,2,6)
          and p.PickupDate  between '2012-01-01' and '2012-01-31' 
    THEN 1 
    ELSE 0
    END)
/ 
COUNT(PickupID)
as 'Pickups as % of Active'

EDIT: OK, I see that I was wrong the first time. You could get a count of active as a subquery instead:

SELECT
max(a.AgencyName)as Agency,
COUNT(PickupID)as Pickups,

COUNT(PickupID) / ActiveClients AS 'Pickups as % of Active',

count (CASE WHEN p.CategCode = 'NB' THEN p.CategCode END) as NB
,count (CASE WHEN p.CategCode = 'IN' THEN p.CategCode END) as 'IN'
,count (CASE WHEN p.CategCode = 'CH' THEN p.CategCode END) as 'CH'
,count (CASE WHEN p.CategCode = 'PG' THEN p.CategCode END) as 'PG'
,count (CASE WHEN p.CategCode = 'BF' THEN p.CategCode END) as 'BF'
,count (CASE WHEN p.CategCode = 'PP' THEN p.CategCode END) as 'PP'
,count (CASE WHEN p.CategCode = 'SR' THEN p.CategCode END) as 'SR'

,count (CASE WHEN p.EthnCode = 'N' THEN p.EthnCode END) as Nat
,count (CASE WHEN p.EthnCode = 'A' THEN p.EthnCode END) as Asn
,count (CASE WHEN p.EthnCode = 'B' THEN p.EthnCode END) as Blk
,count (CASE WHEN p.EthnCode = 'P' THEN p.EthnCode END) as Pac
,count (CASE WHEN p.EthnCode = 'W' THEN p.EthnCode END) as Wth
,count (CASE WHEN p.EthnCode NOT IN ('N', 'A', 'B', 'P', 'W', '0') THEN p.EthnCode END) as Nat
,count (CASE WHEN p.EthnCode = '0' THEN p.EthnCode END) as Unknown
,count (CASE when PickupHispanic=1 then PickupHispanic end) as Hispanic 

FROM dbo.Pickup p join Agency a on p.agencyid = a.agencyid 

join 
(select c.ClientID, COUNT( c.ClientID) AS ActiveClients 
    FROM dbo.Pickup p 
    JOIN Clients c on c.ClientID = p.ClientID
    WHERE 
        c.StatusID in (1,2,6) 
        and p.PickupDate  between '2012-01-01' and '2012-01-31' 
    GROUP BY c.ClientID
) ActiveClientCounts
ON p.ClientID = ActiveClientCounts.ClientID

where  PickupDate  between '2012-01-01' and '2012-01-31' 
group by a.AgencyID 

WITH ROLLUP
order by 1

right now it shows `0` for all records in 'Pickups as % of Active' — Andrey, Mar 14 '13 at 01:23
Changed my approach. Basically, you already have the pickups. You just need to divide that by the active clients, which you can get with a subquery. I might have typed it wrong - just make yourself a query that gets what you want and add the SQL at the bottom. — Neil Mussett, Mar 14 '13 at 01:39
`Msg 8120, Level 16, State 1, Line 5 Column 'ActiveClientCounts.ActiveClients' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.` — Andrey, Mar 14 '13 at 01:41
And I was confuse you with this line in subquery `and p.PickupDate between '2012-01-01'and '2012-01-31'` It shouldn't be there — Andrey, Mar 14 '13 at 01:42
its throws error `Msg 207, Level 16, State 1, Line 34Invalid column name 'ClientID'.` doent like this line `ON p.ClientID = ActiveClientCounts.ClientID` — Andrey, Mar 14 '13 at 01:49

Simon C · Accepted Answer · 2013-03-14T19:38:54.670

1

The problem is that Count results in an integer. And when you divide an integer by an integer it gives you an integer as a result.

Try casting your denominator as a decimal and it will give you a decimal as output:

 select 100 * COUNT(PickupID)/CAST(COUNT( c.ClientID) AS DECIMAL)

EDIT:

Okay, you've changed your code a few times which is quite confusing.

From what I can see at the moment, your error comes from the Group By c.AgencyID clause in the subquery. How about this:

( COUNT(PickupID)/(select Cast(COUNT( c.ClientID) AS DECIMAL) AS ActiveClients 
    FROM Clients c
    WHERE c.StatusID in (1,2,6) 
    AND c.AgencyID = a.AgencyID )) as 'Pickups as % of Active',

EDIT 2:

with the casting for a decimal result, and the casting for 2dp:

CAST(( COUNT(PickupID)/(select CAST(COUNT( c.ClientID) AS DECIMAL) AS ActiveClients 
    FROM Clients c
    WHERE c.StatusID in (1,2,6) 
    AND c.AgencyID = a.AgencyID )) as Decimal(10,2)) as 'Pickups as % of Active',

edited Mar 14 '13 at 19:38

answered Mar 14 '13 at 01:23

Simon C

9,458
3
36
55

It shows `100.0000000000000000000` for every single agency which is can't be right – Andrey Mar 14 '13 at 01:26
You get an integer when you divide 2 by 3? – Dan Bracuk Mar 14 '13 at 02:06
Yep. See this: http://stackoverflow.com/questions/723215/why-does-sql-server-round-off-results-of-dividing-two-integers and this: http://stackoverflow.com/questions/3443672/integer-division-in-sql-server – Simon C Mar 14 '13 at 02:29
@SimonC sorry for changing but it throws me error `Msg 8134, Level 16, State 1, Line 5 Divide by zero error encountered. Warning: Null value is eliminated by an aggregate or other SET operation` – Andrey Mar 14 '13 at 02:56
if i delete `WITH ROLLUP` it is calculating but again not right it gives me only `0` – Andrey Mar 14 '13 at 03:26
@SimonC I combined your statements and make it work :) However it still shoving % as long number with many numbers after comma how to show only 2 numbers after coma? I used `round` but it still shows like this `78.7900000000000000000` – Andrey Mar 14 '13 at 03:37
You can cast the result again as a `decimal(x,2)` or `numeric(x,2)` where x is the number of total digits to show. The 2 (it can be any number) is the number of decimal places. I have shown it in an edit. – Simon C Mar 14 '13 at 19:36

% calculation in query with join and group by clause

2 Answers2