i get multiple count in same row

[kkshansid]

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

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

[code green]

What about something like

SELECT SUM(IF(result=pass,1,0)) AS cp,
SUM(IF(result=fail,1,0)) as cf,
SUM(IF(result=absent,1,0)) as ca

Not sure if this will work

[Atli]

@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

[code green]

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

[kkshansid]

@Atli

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
FROM cm0909
WHERE (((cm0909.YEAR)=2008) AND ((cm0909.STU_TYPE)=1) AND ((cm0909.SEX_CODE)="M"));

THIS GIVES ERROR
ERROR UNDEFINED FUNCTION IF IN EXPRESSION

[Atli]

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.)

[kkshansid]

@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

[kkshansid]

@kkshansid
thanx a lot for ur valuable guidance bt kindly solve my import problem

[Atli]

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.

[Atli]

@kkshansid
Discussing two separate topics in the same thread is generally not a good idea, and we avoid that whenever possible.

So if you want to start a discussion on your import problems, please post your question in a new thread.
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.