php mysql数据库选择语句

Question

Hey there I’m to sort out a php mysql message database with fields:

id, sms_text, receiver _number, time_sent, status.

I want to have a total count of that day (curdate) records of the number sms sent with number of successfully delivered and the number of failed. i.e. Total sms sent ____ , ______ delivered , ____failed

Howerver,

SELECT DATE(time_sent) AS date, SUM() AS total_sales 
  SELECT DATE(time_sent) AS date, SUM(status) AS total_sent from smsdb; 

does not seem to sort it out.

Kindly, any one help?

Answer 1

In MySQL boolean counts as 1(for true) and 0(for false) , so you can basically do this:

SELECT DATE(time_sent) AS `date`,
       COUNT(*) as total_sent,
       SUM(status = 'succesfuly') as `delivered`,
       SUM(status = 'failed') AS `failed` 
FROM smsdb
GROUP BY DATE(time_sent) 

I didn't know the status column options, change it from succesfuly,failed to the actual ones.

I didn't understand what you were trying to do with your query, but you had a few problems there.

SUM() -- you have to specify what to sum

And you were missing a group by clause.