CODEWITHSUNDEEP
mysql
Rajib Deb
Rajib Deb

Reputation: 51

How to get summary data for every months in mysql

I want to count the number of items sold(item_count) every month for every item,

--
-- Table structure for table `sales`
--
CREATE TABLE `sales` (
  `id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `item_count` int(11) NOT NULL,
  `amount` float NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`id`, `item_id`, `date`, `item_count`, `amount`)     VALUES
(1, 1, '2018-01-15', 11, 110),
(2, 2, '2018-01-21', 5, 1000),
(3, 1, '2018-02-02', 7, 700),
(4, 2, '2018-02-11', 3, 3000);

I have tried this SQL, but it's not showing the data correctly.

SELECT `sales`.`item_id`,
(CASE WHEN MONTH(sales.date)=1 THEN sum(sales.item_count) ELSE NULL END) as JAN,
(case when MONTH(sales.date)=2 THEN sum(sales.item_count) ELSE NULL END) as FEB 
FROM sales WHERE 1
GROUP BY sales.item_id
ORDER BY sales.item_id

This is my expected result,

item_id  JAN  FEB
1        11   7
2        5    3

I am getting this,

item_id  JAN  FEB
1        18   NULL
2        8    NULL

Upvotes: 0

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Here is an immediate fix to your query. You need to sum over a CASE expression, rather than the other way around.

SELECT
    s.item_id,
    SUM(CASE WHEN MONTH(s.date) = 1 THEN s.item_count END) AS JAN,
    SUM(CASE WHEN MONTH(s.date) = 2 THEN s.item_count END) AS FEB
FROM sales s
GROUP BY
    s.item_id
ORDER BY
    s.item_id;

But the potential problem with this query is that in order to support more months, you need to add more columns. Also, if you want to cover mulitple years, then this approach also might not scale. Assuming you only have a few items, here is another way to do this:

SELECT
    DATE_FORMAT(date, '%Y-%m') AS ym,
    SUM(CASE WHEN item_id = 1 THEN item_count END) AS item1_total,
    SUM(CASE WHEN item_id = 2 THEN item_count END) AS item2_total
FROM sales
GROUP BY
    DATE_FORMAT(date, '%Y-%m');

This would generate output looking something like:

ym       item1_total  item2_total
2018-01  11           5
2018-02  7            3

Which version you use depends on how many months your report requires versus how many items might appear in your data.

Upvotes: 2

Related Questions