Navjot Singh Navjot Singh - 4 months ago 14
SQL Question

Getting wrong count from query

Table structure and sample data

CREATE TABLE IF NOT EXISTS `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`restaurant_id` int(11) NOT NULL,
`bill_id` int(11) NOT NULL,
`source_id` int(1) NOT NULL,
`order_medium_id` int(11) NOT NULL,
`purchase_method` varchar(255) NOT NULL,
`totalamount` int(11) NOT NULL,
`delivery_charg` int(11) NOT NULL,
`discount` int(11) NOT NULL,
`vat` int(11) NOT NULL,
`total_price` int(11) NOT NULL DEFAULT '0',
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `customer_id` (`customer_id`),
KEY `source_id` (`source_id`),
KEY `restaurant_id` (`restaurant_id`),
KEY `bill_id` (`bill_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=22 ;

--
-- Dumping data for table `orders`
--
INSERT INTO `orders` (`id`, `customer_id`, `restaurant_id`, `bill_id`, `source_id`, `order_medium_id`, `purchase_method`, `totalamount`, `delivery_charg`, `discount`, `vat`, `total_price`, `date_created`, `uploaddate`) VALUES
(1, 1, 1, 1, 1, 0, 'cash', 1600, 0, 0, 0, 1600, '2016-05-29 13:05:40', '2016-07-07 05:55:39'),
(2, 1, 1, 2, 2, 1, 'cash', 1820, 0, 0, 0, 1820, '2016-06-27 07:21:25', '2016-07-07 05:55:39'),
(4, 1, 1, 3, 3, 0, 'cash', 1770, 0, 0, 0, 1770, '2016-05-31 13:05:56', '2016-07-07 05:55:39'),
(5, 1, 1, 4, 2, 1, 'cash', 1300, 0, 0, 0, 1300, '2016-06-27 07:21:31', '2016-07-07 05:55:39'),
(6, 1, 1, 5, 1, 0, 'cash', 950, 0, 0, 0, 950, '2016-06-02 13:06:15', '2016-07-07 05:55:39'),
(7, 1, 1, 6, 1, 0, 'cash', 1640, 0, 0, 0, 1640, '2016-06-03 13:06:24', '2016-07-07 05:55:39'),
(8, 1, 1, 7, 2, 2, 'cash', 1600, 0, 0, 0, 1600, '2016-06-27 07:21:36', '2016-07-07 05:55:39'),
(9, 1, 1, 8, 2, 2, 'cash', 1575, 0, 0, 0, 1575, '2016-06-27 07:21:40', '2016-07-07 05:55:39'),
(10, 1, 1, 9, 3, 0, 'cash', 1125, 0, 0, 0, 1125, '2016-06-06 13:06:48', '2016-07-07 05:55:39'),
(11, 1, 1, 10, 2, 3, 'cash', 1920, 0, 0, 0, 1920, '2016-06-27 07:21:51', '2016-07-07 05:55:39'),
(12, 1, 1, 11, 3, 0, 'cash', 1560, 0, 0, 0, 1560, '2016-06-08 13:07:05', '2016-07-07 05:55:39'),
(13, 1, 1, 12, 2, 4, 'cash', 1365, 0, 0, 0, 1365, '2016-06-27 07:21:56', '2016-07-07 05:55:39'),
(14, 1, 1, 13, 1, 0, 'cash', 1235, 0, 0, 0, 1235, '2016-06-10 13:07:26', '2016-07-07 05:55:39'),
(15, 1, 1, 14, 3, 0, 'cash', 1595, 0, 0, 0, 1595, '2016-06-11 13:07:34', '2016-07-07 05:55:39'),
(16, 1, 1, 15, 3, 0, 'cash', 1900, 0, 0, 0, 1900, '2016-06-12 13:07:44', '2016-07-07 05:55:39'),
(17, 1, 1, 16, 3, 0, 'cash', 2200, 0, 0, 0, 2200, '2016-06-13 13:07:52', '2016-07-07 05:55:39'),
(18, 1, 1, 17, 1, 0, 'cash', 1475, 0, 0, 0, 1475, '2016-06-14 13:07:59', '2016-07-07 05:55:39'),
(19, 2, 1, 35, 2, 4, 'Online', 471, 0, 0, 0, 471, '2016-07-04 13:20:25', '2016-07-07 05:55:39'),
(21, 4, 1, 36, 1, 0, 'Online Payment', 0, 0, 0, 0, 2000, '2016-07-01 07:06:46', '2016-07-07 05:55:39'),
(22, 1, 1, 40, 2, 1, 'cash', 3920, 80, 200, 100, 3900, '2016-07-06 13:10:36', '2016-07-07 05:55:39'),
(23, 5, 1, 42, 2, 2, 'Cash', 0, 0, 0, 0, 2620, '2016-07-11 06:08:24', '2016-07-11 12:31:02'),
(24, 2, 1, 48, 3, 0, 'cash', 0, 0, 0, 0, 1000, '2016-07-12 16:26:00', '2016-07-12 10:56:40'),
(25, 6, 1, 47, 3, 0, 'cash', 0, 0, 0, 0, 2330, '2016-07-12 16:35:00', '2016-07-12 11:05:41'),
(26, 7, 1, 46, 3, 0, 'cash', 0, 0, 0, 0, 1000, '2016-07-12 17:18:00', '2016-07-12 11:48:11');


Requirement :

1. Need to get total number of order for current and last month.


Query i tried

SELECT CASE source_id
WHEN 1
THEN 'visit'
WHEN 2
THEN 'online'
WHEN 3
THEN 'phone'
END AS `type` ,
count( IF( MONTH( date_created ) = MONTH( ( CURDATE() - INTERVAL 1 MONTH ) ) , source_id, 0 )) AS `lastMonthOrders` ,
count( IF( MONTH( date_created ) = MONTH( NOW() ) , source_id, 0 ) ) AS `currentMonthOrders`
FROM `orders`
WHERE MONTH(date_created)
BETWEEN MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )
AND MONTH(CURDATE( ))
AND restaurant_id =1
GROUP BY source_id


Got Result

type lastMonthOrders currentMonthOrders
online 5 5
visit 9 9
phone 8 8


Result Should be

type lastMonthOrders currentMonthOrders
online 6 3
visit 4 1
phone 5 3

Answer

@MarcB explained why COUNT in this case might turn towards wrong output.

Here I've changed the query using SUMinstead of COUNT

SELECT CASE source_id
WHEN 1 
THEN  'online'
WHEN 2 
THEN  'visit'
WHEN 3 
THEN  'phone'
END AS  `type` ,
SUM(MONTH( date_created ) = MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )) AS  `lastMonthOrders` , 
SUM(MONTH( date_created ) = MONTH( NOW() )) AS  `currentMonthOrders` 
FROM  `orders` 
WHERE MONTH(date_created)
BETWEEN MONTH( ( CURDATE() - INTERVAL 1 MONTH ) )
AND MONTH(CURDATE( )) 
AND restaurant_id =1
GROUP BY source_id;

Note:

SUM(a=b) returns 1 only if a=b.

So, in the above query

SUM(MONTH( date_created ) = MONTH( NOW() )) AS currentMonthOrders will add 1 only if date_created and NOW() reflect the same month number

Some subtleties regarding COUNT:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE

Comments