php mysql database select statement

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?

sagi · Accepted Answer · 2016-06-08 12:29:03Z

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.

answered Jun 8, 2016 at 12:29

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user6404968 Over a year ago

Actually from your guidelines, it works like charm. Thank you.

Collectives™ on Stack Overflow

php mysql database select statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related