i get multiple count in same row | Familiar Sight | | Join Date: Oct 2008
Posts: 128
| |
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
| |
best answer - posted by 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
|  | Expert | | Join Date: Mar 2007 Location: England
Posts: 1,079
| | | re: i get multiple count in same row
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
|  | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,747
| | | re: i get multiple count in same row
Quote:
Originally Posted by code green  Not sure if this will work 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
| | Expert | | Join Date: Mar 2007 Location: England
Posts: 1,079
| | | re: i get multiple count in same row
Quote:
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
| | Familiar Sight | | Join Date: Oct 2008
Posts: 128
| | | re: i get multiple count in same row
Quote:
Originally Posted by Atli 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 - 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
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,747
| | | re: i get multiple count in same row
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.) | | Familiar Sight | | Join Date: Oct 2008
Posts: 128
| | | re: i get multiple count in same row
Quote:
Originally Posted by 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.) 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
| | Familiar Sight | | Join Date: Oct 2008
Posts: 128
| | | re: i get multiple count in same row
Quote:
Originally Posted by kkshansid 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 thanx a lot for ur valuable guidance bt kindly solve my import problem
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,747
| | | re: i get multiple count in same row
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.
| | Moderator | | Join Date: Nov 2006 Location: Iceland
Posts: 3,747
| | | re: i get multiple count in same row
Quote:
Originally Posted by kkshansid thanx a lot for ur valuable guidance bt kindly solve my import problem 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.
|  |
Similar MySQL Database bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,414 network members.
|
