CODEWITHSUNDEEP
mysqlsql
user2804374
user2804374

Reputation: 23

How to form this sql query?

SELECT state,count(*) as regular 
FROM X 
group by state

This gives a result like 

AL 10
AK 20 
AZ 25

SELECT state,count(*) as regular 
FROM X 
where alcohol_use=TRUE 
group by state

This gives a result like 

AL 5
AK 10
AZ 20

I need a single query which finds out the percentage and provides me an output like

AL 50%
AK 50%
AZ 80%

Thanks in advance

Upvotes: 2

Views: 34

Answers (2)

Mureinik
Mureinik

Reputation: 311308

One way of achieving this "partial count" is to sum a case expression with your condition. E.g.:

SELECT   state, 
         SUM (CASE alcohol_use WHEN TRUE THEN 1 ELSE 0 END) / COUNT(*) as percent
FROM     x
GROUP BY state

Upvotes: 0

zerkms
zerkms

Reputation: 254926

  SELECT state,
         SUM(alcohol_use=TRUE) / COUNT(*) * 100
    FROM X
GROUP BY state

SUM(alcohol_use=TRUE) expression would add up values of alcohol_use=TRUE which is either true or false which is then implicitly casted to 1 or 0 accordingly.

Upvotes: 2

Related Questions