php mysql数据库选择语句

问题描述：

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?

嘿，我要用字段来整理php mysql消息数据库： p>

  id，sms_text，receiver _number，time_sent，status。
  code>  pre> 
 
 我想得到那天（curdate）记录的总数 发送的号码短信，成功发送的号码和失败的号码。  
i.e。 发送的总短信数____，______发送，____失败 p> 
 
 
 Howerver， p> 
 
 
  SELECT DATE（time_sent）AS date，SUM（）AS total_sales \  n SELECT DATE（time_sent）AS日期，SUM（状态）AS total_sent from smsdb;  
  code>  pre> 
 
 似乎没有将其排除。 p> 
 
 
请帮忙吗？ p> 
   DIV>

答

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.

php mysql数据库选择语句

相关推荐