0

Looking to Divide the Returned and Total columns, to get a return rate On the most returned products. Here is what I have, how do I add the divide function on an alias?

SELECT brand
    ,model
    ,count(*) Total
    ,sum(case when returned = 'Y' then 1 else 0 end) as Returned
    ,sum(case when returned = '' then 1 else 0 end) as Kept

FROM table
WHERE year= '2018'
AND NOT type = 's'
GROUP by model

ORDER by Returned DESC;

Thanks

cmfish
  • 1
  • 5
  • See [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Jan 03 '18 at 00:23

2 Answers2

0

You can wrap it inside another SELECT

SELECT brand
    ,model
    ,count(*) Total
    ,sum(case when returned = 'Y' then 1 else 0 end) as Returned
    ,sum(case when returned = '' then 1 else 0 end) as Kept
    ,(SELECT Returned/Total) as Rate
FROM table
WHERE year= '2018'
AND NOT type = 's'
GROUP by model
ORDER by Returned DESC;
clinomaniac
  • 2,200
  • 2
  • 17
  • 22
0

You can simply do this.

SELECT brand
    ,model
    ,count(*) as Total
    ,sum(case when returned = 'Y' then 1 else 0 end) as Returned
    ,sum(case when returned = 'Y' then 1 else 0 end)/count(*) as returnRate
    ,sum(case when returned = '' then 1 else 0 end) as Kept

FROM table
WHERE year= '2018'
AND NOT type = 's'
GROUP by model
ORDER by Returned DESC;