mookie - 3 months ago 5x

SQL Question

`SELECT type, count(*) as Total`

FROM table

WHERE date between ((YEAR(CURDATE())-1900)*1000+DAYOFYEAR(CURDATE())-30)

AND (YEAR(CURDATE())-1900)*1000+DAYOFYEAR(CURDATE())

AND rp=100

AND pd in ('P7','P9','QU','QL')

GROUP BY type;

This produces:

`**TYPE** **TOTAL**`

A 2

B 4

Is it feasible to edit my query to give me a percentage like A/(A+B). I'm struggling to get A single types total value as the numerator, and the total value of all types as the denominator

Answer

Simple version, a cte using a sub-query returning the table's total number of rows:

```
with cte as
(
select type
from table
where date between ((year(CURDATE())-1900)*1000+DAYOFYEAR(CURDATE())-30)
and (year(CURDATE())-1900)*1000+DAYOFYEAR(CURDATE())
and rp = 100
and pd in ('P7', 'P9', 'QU', 'QL')
)
select type, count(*), 100.0 * count(*) / (select count(*) from cte)
from cte
group by type
```

Source (Stackoverflow)

Comments