CODEWITHSUNDEEP
mysqlnullrows
Homer_J
Homer_J

Reputation: 3323

MySQL Doesn't bring back data

I have the following code:

SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC

Very simple, there can only be four responses in the q1 field - they are 1,2,3 or 4

See the output below:

q1  Responses   Total   percentage
4   109         362     30.1105
3   224         362     61.8785
2   25          362     6.9061
1   4           362     1.1050

Now, this works a treat if options 1,2,3 or 4 are in q1 BUT, the problem I have is if there are no 4's in Q1 - the code will return rows for 3,2 and 1 and not 4 (which I guess is actually correct).

So, if there were no 4's I need it to produce something like this...

q1  Responses   Total   percentage
4   0           253     0
3   224         253     88.53754
2   25          253     9.88142
1   4           253     1.58102

Hope that makes sense.

Upvotes: 2

Views: 78

Answers (1)

jcho360
jcho360

Reputation: 3759

Try this out:

select * from (SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC
union
select 1,0,0,0 from dual
union
select 2,0,0,0 from dual
union
select 3,0,0,0 from dual
union
select 4,0,0,0 from dual) as t group by q1

http://www.sqlfiddle.com/#!2/84b31/6

or you can try without the DUAL

select * from (SELECT q1, COUNT(q1) as Responses, 
(SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1) AS Total, 
(COUNT(q1) / (SELECT COUNT(q1) FROM `acme` WHERE q1 >= 1 )) *100 AS Percentage
FROM `acme` WHERE q1  >= 1 GROUP BY q1 DESC
union
select 1,0,0,0
union
select 2,0,0,0
union
select 3,0,0,0
union
select 4,0,0,0) as t group by q1;

http://www.sqlfiddle.com/#!2/97031/19

Upvotes: 1

Related Questions