combine into single SQL statement

Thread: combine into single SQL statement

1. Senior Member
Join Date
Dec 1969
Posts
141

combine into single SQL statement

Is it possbile to combine <BR>select count(rate) from poll where rate =1 <BR>select count(rate) from poll where rate =2<BR>select count(rate) from poll where rate =3<BR>select count(rate) from poll where rate =4<BR>select count(rate) from poll where rate =4<BR><BR>into one single SQL statment...<BR><BR>ThanQ

2. Senior Member
Join Date
Dec 1969
Posts
16,931

RE: combine into single SQL statement

I think you want:<BR><BR>SELECT (select count(*) from poll where rate =1), <BR>(select count(*) from poll where rate =2),<BR>(select count(*) from poll where rate =3),<BR>(select count(*) from poll where rate =4)<BR><BR>However what happens if rate=5, or 6, or 7? You probably want to investigate the use of SUBQUERIES to do what you want. Would go something like:<BR><BR>SELECT DISTINCT rate, (SELECT COUNT(*) FROM poll WHERE rate=P.rate) FROM poll AS P<BR><BR>Craig,

3. Senior Member
Join Date
Dec 1969
Posts
3,195

or maybe something like

SELECT rate, count(rate) <BR>FROM poll <BR>WHERE rate IN (1,2,3,4)<BR>GROUP BY rate<BR><BR>OR<BR>(assuming whole numbers 1,2,etc.)<BR><BR>SELECT rate, count(rate) <BR>FROM poll <BR>WHERE rate &#062; 0 AND rate &#060; 5<BR>GROUP BY rate<BR><BR>

4. Senior Member
Join Date
Dec 1969
Posts
141

RE: or maybe something like

what i need is define rate = 1 as A<BR>rate = 2 as B<BR>rate = 3 as C<BR>rate = 4 as D<BR><BR>then i will show the result as<BR><BR> A B C D<BR>count 1 3 4 5<BR><BR>is it possible <BR>ThanQ<BR>Winnie

5. Senior Member
Join Date
Dec 1969
Posts
141

RE: combine into single SQL statement

for SELECT (select count(*) from poll where rate =1), <BR>(select count(*) from poll where rate =2))<BR><BR>it comes up with error message<BR><BR>You have an error in your SQL syntax near &#039;<BR>select count(*) from poll where rate =1), <BR>(select count(*) from poll where rat&#039; at line 1".<BR><BR>Pls help ThanQ

6. Senior Member
Join Date
Dec 1969
Posts
2,437

Which DBMS?

&nbsp;<BR>select sum(case when rate = 1 then 1 else 0 end) as A,<BR>sum(case when rate = 2 then 1 else 0 end) as B,<BR>sum(case when rate = 3 then 1 else 0 end) as C,<BR>sum(case when rate = 4 then 1 else 0 end) as D<BR>from poll where rate in (1,2,3,4)

7. Senior Member
Join Date
Dec 1969
Posts
141

RE: Which DBMS?

cool .. it works... i am using mysql

8. Senior Member
Join Date
Dec 1969
Posts
2,437

RE: Which DBMS?

That&#039;s what i expected from the error message. Mysql does not support any form of subqueries.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•