1

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
just10minutes
  • 583
  • 10
  • 26

1 Answers1

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 |
D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • Thank you @D-Shih for the explanation. I have accepted your answer. – just10minutes Aug 14 '18 at 21:23
  • I am not sure is there a better way to handle this if I have multiple categories lets say 10 categories, then with this method we have to write our case statement twice, one for actual value and one for Group by. This is just a general question – just10minutes Aug 14 '18 at 21:26
  • @just10minutes What's you mean about `multiple categories lets say 10 categories` could you give me some example? – D-Shih Aug 14 '18 at 21:30
  • For others who might end up here, here is the detailed explanation of thos concept https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by – just10minutes Aug 14 '18 at 21:30
  • Yes you can use `group by` alias name in mysql http://sqlfiddle.com/#!9/faf2f/6990 – D-Shih Aug 14 '18 at 21:32
  • Like 0-100, 100-200,200-300,300-400 and so on instead of Just 0-300 and >300 – just10minutes Aug 14 '18 at 21:34
  • you can use multiple `WHEN` in the `CASE WHEN` http://sqlfiddle.com/#!9/faf2f/6992 – D-Shih Aug 14 '18 at 21:35
  • So in my case I could have got proper result if I would have used Cnt/(select count(*) from Cars) instead of Cnt/sum(Cnt) Thank you for the tip – just10minutes Aug 14 '18 at 21:36
  • The main query does not actually need to be a subquery, you could just do `FROM Cars CROSS JOIN (...) v` and `count(*), count(*)/v.totle * 100` _Also, I believe MySQL is (thankfully) deprecating the `ORDER BY [fieldnum]` notation._ – Uueerdo Aug 14 '18 at 21:50