472,127 Members | 1,556 Online

# i get multiple count in same row

232 100+
i hav a table with a column named result where values are either of the three
pass ,fail,absent

can i get multiple count in same row
like
Expand|Select|Wrap|Line Numbers
1. select count(result=pass) as cp,count(result=fail) as cf,count(result=absent) as ca;
i know about three select count statements with three where clause
Sep 22 '09 #1

#### ✓ answered by code green

Expand|Select|Wrap|Line Numbers
1. SELECT SUM(IF(result=pass,1,0)) AS cp,
2. SUM(IF(result=fail,1,0)) as cf,
3. SUM(IF(result=absent,1,0)) as ca
Not sure if this will work

9 2875
code green
1,726 Expert 1GB
Expand|Select|Wrap|Line Numbers
1. SELECT SUM(IF(result=pass,1,0)) AS cp,
2. SUM(IF(result=fail,1,0)) as cf,
3. SUM(IF(result=absent,1,0)) as ca
Not sure if this will work
Sep 22 '09 #2
Atli
5,058 Expert 4TB
@code green
It does. I just tried it. (Had to know if it worked xD)
You forgot to quote the strings tho.

Nice thinking, btw. My mind was already going for multiple self joins or sub-queries :P
Sep 22 '09 #3
code green
1,726 Expert 1GB
Nice thinking, btw. My mind was already going for multiple self joins or sub-queries :P
Thanks! Multiple self-join was also in my thoughts. Probably got solution because I am always confusing COUNT and SUM
Sep 23 '09 #4
kkshansid
232 100+
@Atli
Expand|Select|Wrap|Line Numbers
1. SELECT COUNT( IF(PASS_FAIL='P',1,0)) AS cp,COUNT ( IF(PASS_FAIL='F',1,0)) as cf,COUNT( IF(PASS_FAIL='A',1,0)) as ca
2. FROM cm0909
3. WHERE (((cm0909.YEAR)=2008) AND ((cm0909.STU_TYPE)=1) AND ((cm0909.SEX_CODE)="M"));
THIS GIVES ERROR
ERROR UNDEFINED FUNCTION IF IN EXPRESSION
Sep 30 '09 #5
Atli
5,058 Expert 4TB
Which database system are you using?
Only hits I can get on that error are related to Access, not MySQL.

In which case you could try IIF instead of IF. (I think.)
Oct 1 '09 #6
kkshansid
232 100+
@Atli
ya m using access temperorily bcoz i cant import data in mysql
as my sql limit is (Max: 2,048KB)
and my file size is 11.1 mb
kindly also suggest how to import this file
Oct 1 '09 #7
kkshansid
232 100+
@kkshansid
thanx a lot for ur valuable guidance bt kindly solve my import problem
Oct 1 '09 #8
Atli
5,058 Expert 4TB
Well, MySQL queries and Access queries aren't exactly the same.
You shouldn't expect MySQL queries to work flawlessly under Access, nor the other way around.

Also, these sort of details are handy for us to have before hand, so we don't waste time debugging Access errors for perfectly fine MySQL queries.
Oct 1 '09 #9
Atli
5,058 Expert 4TB
@kkshansid
Discussing two separate topics in the same thread is generally not a good idea, and we avoid that whenever possible.

Make sure to post all relevant information, such as the details on your MySQL server, your previous attempts to import your data and why/how you were unsuccessful.

Thanks.
Oct 1 '09 #10