SQL - Calculate percentage after Group by - Only one row is shown

Question

I am fiddling on SQL fiddle with group by clause and finding the percentage based on the temp table "A" result.

Here is the fiddle.

http://sqlfiddle.com/#!9/faf2f/6959

I did come across that there are different ways of achieving this like by using union all but my question is why this query brings back only one row instead of two rows.

• List item

Data:

TotalCost
230
200
100
1254

Query:

SELECT Category, Cnt , Cnt/sum(Cnt)  as percent from (

SELECT 
  Case When TotalCost < 301 Then '0-300'
  Else ' > 300' End as Category,
  count(*) as cnt
  FROM Cars      
  group by Category       

  ) A
;

Expected Result:

Category    Cnt   percent
0-300        3     75
> 300        1     25

Actual Result:

Category    Cnt  percent
> 300        1    25

Answer 1

1. you can try to group by case when instead of an alias name.
2. seconde your total count need to do all count so you can do a subquery.

look like this.

SELECT Category, Cnt , Cnt/(select count(*) from Cars) * 100  as percent 
from (
  SELECT 
  (Case When TotalCost < 301 Then '0-300'
  Else ' > 300' End) as Category,
  count(*) as cnt
  FROM Cars
  GROUP BY  (Case When TotalCost < 301 Then '0-300'
  Else ' > 300' End)
) A
ORDER BY 3 DESC

or you can use CROSS JOIN to get the total.

SELECT Category, Cnt , Cnt/v.totle * 100  as percent 
from (
  SELECT 
  (Case When TotalCost < 301 Then '0-300'
  Else ' > 300' End) as Category,
  count(*) as cnt
  FROM Cars
  GROUP BY  (Case When TotalCost < 301 Then '0-300'
  Else ' > 300' End)
) A CROSS JOIN (select count(*) totle from Cars) v

sqlfiddle

Results:

| Category | Cnt | percent |
|----------|-----|---------|
|    0-300 |   3 |      75 |
|    > 300 |   1 |      25 |