0

This is my table structure:

id  customer  date_received  completed  date_collected

18  14       2018-08-18       no        2018-08-23
19  15       2018-08-18       no        2018-08-23
24  18       2018-08-18       no        2018-08-21
25  17       2018-08-10       Yes       2018-08-16
26  19       2018-08-18       Yes       2018-08-23
27  20       2018-08-17       Yes       2018-08-22

My query : 

SELECT COUNT(date_collected) FROM `order`
WHERE `date_collected`="2018-09-05" AND `completed`='No'

I want the number of entry by date collected only complete (no). like date_collected is 2018-08-23 number of count of complete no is 2

halfer
  • 19,824
  • 17
  • 99
  • 186
Er Vaibhav Vatsa
  • 9
  • 6
  • https://stackoverflow.com/q/10599789/2943403 and https://stackoverflow.com/q/10395444/2943403 and https://stackoverflow.com/q/16584549/2943403 – mickmackusa Sep 05 '18 at 03:17
  • https://stackoverflow.com/q/28726329/2943403 – mickmackusa Sep 05 '18 at 03:25
  • 1
    Possible duplicate of [mySQL count only returning one result unless using group by](https://stackoverflow.com/questions/40503132/mysql-count-only-returning-one-result-unless-using-group-by) – mickmackusa Sep 05 '18 at 03:46

1 Answers1

0

You can use a query like below to meet your requirement...

SELECT *, COUNT(`date_collected`) AS not_collected_count
FROM `your_table` 
WHERE `completed` = 'no' 
GROUP BY `date_collected`;

GROUP BY will help to group similar dates

COUNT will help to get the number of entries in that date

Here is a SQL Fiddle Demo

Sinto
  • 3,915
  • 11
  • 36
  • 70