how can I avoid column which contain null or zero value

Question

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

score 3 · Answer 1 · answered Jun 10 '16 at 12:16

A SQL query returns a fixed set of columns, determined by the from clause. If you want a variable set of columns, then you can use dynamic SQL.

In your case, perhaps getting the results as columns would suffice:

select month(date_created), sum(total_price)
from orders
where source_id = 1 and
      date_created BETWEEN(CURDATE() - INTERVAL 1 MONTH) and CURDATE()
group by month(date_created)
having sum(total_price) <> 0;

how can I avoid column which contain null or zero value

here the table structure

-- Table structure for table orders

1 Answers1

-- Table structure for table `orders`