Pankaj katiyar Pankaj katiyar - 5 months ago 8
MySQL Question

how can I avoid column which contain null or zero value

I want to avoid those column which contain null or zero value

here the table structure



-- Table structure for table
orders



CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`restaurant_id` int(11) NOT NULL,
`source_id` int(1) NOT NULL,
`purchase_method` varchar(255) NOT NULL,
`total_price` int(11) NOT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `customer_id`, `restaurant_id`, `source_id`, `purchase_method`, `total_price`, `date_created`) VALUES
(1, 1, 1, 3, 'Cash', 500, '2016-05-31 11:44:16'),
(2, 1, 1, 3, 'Cash', 1500, '2016-06-01 11:44:22'),
(3, 1, 1, 3, '', 650, '2016-06-02 11:44:26'),
(4, 1, 1, 2, 'cash', 1500, '2016-06-03 11:44:31'),
(5, 1, 1, 1, 'cash', 12000, '2016-06-04 21:08:00'),
(6, 1, 1, 1, 'cash', 14500, '2016-06-05 00:00:00'),
(7, 1, 1, 2, 'cash', 15000, '2016-06-10 09:47:15'),
(8, 1, 1, 2, 'cash', 14500, '2016-05-10 10:03:55'),
(9, 1, 1, 1, 'cash', 11800, '2016-06-08 00:00:00'),
(10, 1, 1, 2, 'ss', 300, '2016-06-08 01:06:56'),
(11, 1, 1, 1, 'online', 400, '2016-05-10 10:03:20'),
(12, 1, 1, 3, 'cash', 5000, '2016-06-09 06:23:16'),
(13, 1, 1, 2, 'cash', 2000, '2016-05-10 10:03:35'),
(14, 1, 1, 1, 'cash', 499, '2016-04-11 18:30:00'),
(15, 1, 1, 1, 'cash', 2010, '2016-03-11 18:58:00'),
(16, 1, 1, 1, 'cash', 599, '2016-03-11 18:30:00'),
(17, 1, 1, 1, 'online', 699, '2016-05-02 18:30:00');

-- --------------------------------------------------------

the query I tried below it


SELECT
SUM(CASE WHEN MONTH(date_created)=1 THEN (total_price) END) Jan,
SUM(CASE WHEN MONTH(date_created)=2 THEN (total_price) END) Feb,
SUM(CASE WHEN MONTH(date_created)=3 THEN (total_price) END) Mar,
SUM(CASE WHEN MONTH(date_created)=4 THEN (total_price) END) Apr,
SUM(CASE WHEN MONTH(date_created)=5 THEN (total_price) END) May,
SUM(CASE WHEN MONTH(date_created)=6 THEN (total_price) END) Jun,
SUM(CASE WHEN MONTH(date_created)=7 THEN (total_price) END) July,
SUM(CASE WHEN MONTH(date_created)=8 THEN (total_price) END) Aug,
SUM(CASE WHEN MONTH(date_created)=9 THEN (total_price) END) Sep,
SUM(CASE WHEN MONTH(date_created)=10 THEN (total_price) END) 'Oct',
SUM(CASE WHEN MONTH(date_created)=11 THEN (total_price) END) Nov,
SUM(CASE WHEN MONTH(date_created)=12 THEN (total_price) END) 'Dec'
FROM orders
WHERE source_id =1 AND date_created BETWEEN(CURDATE() - INTERVAL 1 MONTH)
AND CURDATE()


Result I get from query

Jan Feb Mar Apr May Jun July Aug Sep Oct Nov Dec
null null null null null 38300 null null null null null null null


desire result

may june
0 38300

Answer

Here I got desire answer query link

SELECT 
    CASE source_id WHEN 1 THEN 'online' WHEN 2 THEN 'visit' WHEN 3 THEN 'phone' END AS `Type`,
    SUM(IF(MONTH(date_created) = MONTH(CURDATE() - INTERVAL 1 MONTH), total_price, 0)) AS `May`,
    SUM(IF(MONTH(date_created) = MONTH(CURDATE()), total_price, 0)) AS `June`
FROM `orders`
WHERE date_created BETWEEN (CURDATE() - INTERVAL 1 MONTH) AND CURDATE()
GROUP BY source_id 
Comments