PHP/MYSQL COUNT question

Gregg Battaglia
Gregg Battaglia used Ask the Experts™
on
I am having a little trouble figuring out the COUNT function in PHP and MYSQL... I am new to both. The table I am using is "students" and the colum is "Called_Received" in the colum is just a 1 or 0 for yes/no... I am trying to use the SELECT and COUNT to get three numbers:

Total=? Yes=? No=?

I want the labels to be: "Total", "OK", "NA"

someone pointed me in the direction of this:

SELECT Called_Received COUNT "*", COUNT "Called_Received=1", COUNT "Called_Received=0" FROM students

This of course does not work. I was told this is a simple function but I'm just having a hard time with it. I would really appciate it if someone could help me out with this SQL statement. Hopefully I described this well enough.

Thanks
Gregg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT Count(*), Called_Received
FROM students
Group By Called_Received
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
select sum(1) called_total
, sum(case when called_received = 1 then 1 else 0 end) call_received
, sum(case when called_received = 1 then 0 else 1 end) call_not_received
from students
so you're doing three count operations on different data.  
one is a count of all records,
one is a count of all yes marked Records
one is a count of all No marked Records

sure
select Max(Total), Max(OK), max(NA) from
(SELECT count(*) as Total , "0" as OK, "0" as NA from Student
UNION
Select "0" as Total, count(*) as OK, "0" as NA from Student where Called_received = 1
UNION
Select "0" as Total, "0" as OK, count(*) as NA from Student where Called_Received=0)


What this does is actually create a nifty little view with the folowing contents
T     Y    N
9     0     0
0     4     0
0     0     5

and then take the largest one. from each column and you'll get
T     Y     N
9     4     5

It's not pretty but it will work.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

angelIII:  That's a pretty elegant way to do it.  Nice.

Author

Commented:
ok... so far this is what I got for each one:
==================================================================================
SELECT Count(*), Called_Received
FROM students
Group By Called_Received
=================================================================================
Result: this one gave me the results in the almost the way I need it. What happens here is it returns 2 records. but I need three records, (Total,OK,NA) doing it this way returns two records one for the total of each (OK,NA) but no grand total.


The next one was:
=================================================================================
select sum(1) called_total
, sum(case when called_received = 1 then 1 else 0 end) call_received
, sum(case when called_received = 1 then 0 else 1 end) call_not_received
from students
=================================================================================
Result: This one gave me the data I wanted but it is all in one record. This one worked well, but for me to use it I need three different records. If there was a way to break this up into three records it would be perfect.


the last one was:
================================================================================
select Max(Total), Max(OK), max(NA) from
(SELECT count(*) as Total , "0" as OK, "0" as NA from Student
UNION
Select "0" as Total, count(*) as OK, "0" as NA from Student where Called_received = 1
UNION
Select "0" as Total, "0" as OK, count(*) as NA from Student where Called_Received=0)
=================================================================================
Result: This one didn't work for me. It looks like it should work but when I put it into Dreamweaver it comes back with an error of "MySQL Error#:1248 - Every delivered table must have its own alias"

Any ideas???
I am using a program to create a dynamic chart and it needs one record for each bar of the graph.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:

SELECT sum(total) as total, sum(ok) as ok, sum(NA) as na
FROM (
SELECT count(*) as Total , 0 as OK, 0 as NA from Student
UNION ALL
Select 0 , count(*), 0 from Student group where Called_Received=1
UNION ALL
Select 0 , 0, count(*) from Student group where Called_Received=0
) l

Author

Commented:
using that one I get this error:
==================================================================================
MySQL Error#:1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax use near 'where Called_Recieved=1 UNION ALL Select 0, 0 count(*) from Student group wher' at line 1
===================================================================================

This is all inside dreamweaver on php pages. I'm not sure if the syntax is different?
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry...

SELECT sum(total) as total, sum(ok) as ok, sum(NA) as na
FROM (
SELECT count(*) as Total , 0 as OK, 0 as NA from Student
UNION ALL
Select 0 , count(*), 0 from Student where Called_Received=1
UNION ALL
Select 0 , 0, count(*) from Student where Called_Received=0
) l

Author

Commented:
doing it with this:
================================================
SELECT sum(Total) as Total, sum(OK) as OK, sum(NA) as NA FROM ( SELECT count(*) as Total , 0 as OK, 0 as NA
FROM Students UNION ALL Select 0 , count(*), 0 from Students
WHERE Called_Received=1 UNION ALL Select 0 , 0, count(*) from Students where Called_Received=0 ) l
===================================================================

give me the same results as before. it returns the results in one record, and I need it in 3 records. do you think this can be done? BTW I really appreciate all of your help.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I think I need more coffee :-)

SELECT count(*) as Total , 'Total' as group from Student
UNION ALL
Select count(*), 'OK' from Student where Called_Received=1
UNION ALL
Select count(*), 'NA' from Student where Called_Received=0

Author

Commented:
I tried this:
==================================================
SELECT count(*) as Total , 'Total' as group from Students
UNION ALL
SELECT count(*), 'OK' FROM Students WHERE Called_Received=1
UNION ALL
Select count(*), 'NA' from Students where Called_Received=0

=========================================================================

and I get this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group from Students UNION ALL Select count(*), 'OK' from Students where Called_R' at line 1

=================================================================================

I just got more coffee as I'm banging my head on the desk at this point. :-)
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
looks like "group" needs escaping:

SELECT count(*) as `Total` , 'Total' as `group` from Students
UNION ALL
SELECT count(*), 'OK' FROM Students WHERE Called_Received=1
UNION ALL
Select count(*), 'NA' from Students where Called_Received=0

Author

Commented:
YOU ROCK!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial