mookie mookie - 1 year ago 89
SQL Question

Editing query to include math

SELECT type, count(*) as Total
FROM table
WHERE date between ((YEAR(CURDATE())-1900)*1000+DAYOFYEAR(CURDATE())-30)
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download