i want to get some row values as column day wise for one month

Question

i want to get some row values as column sum of which column is reading date basis

SELECT * FROM `mitre_details`;

id       reading            date
1         500               2019-07-15
2         300               2019-07-15
3         600               2019-07-15
4         800               2019-07-15
5         900               2019-07-15
1         200               2019-07-16
2         250               2019-07-16
3         400               2019-07-16
4         550               2019-07-16
5         790               2019-07-16

Tried Query:

SELECT date,sum(reading) FROM `mitre_details` GROUP BY date;

Desired Output:

Date          Reading    Mitre 1    Mitre 2   Mitre 3   Mitre 4   Mitre 5
2019-07-15     3100       500         300       600       800       900
2019-07-16     2190       200         250       400       550       790

Seriously consider handling issues of data display in application code. Also, your query cannot produce the sample result shown. — Strawberry, Jul 16 '19 at 09:56
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Jul 16 '19 at 09:58
i need date wise all mitre reading show and sum of all mitre reading per day — mrinmoy, Jul 16 '19 at 10:05

James · Accepted Answer · 2019-07-16T11:39:30.477

0

Try this,

select `date`,
sum(reading) Reading,
group_concat(case when id=1 then reading end) m1,
group_concat(case when id=2 then reading end) m2,
group_concat(case when id=3 then reading end) m3,
group_concat(case when id=4 then reading end) m4,
group_concat(case when id=5 then reading end) m5
from mitre_details group by `date`;

edited Jul 16 '19 at 11:39

answered Jul 16 '19 at 10:30

James

1,819
2
8
21

mitre 1 value is comming rest of mitre value is null – mrinmoy Jul 16 '19 at 11:05
can you share create tbl schema – James Jul 16 '19 at 11:06
CREATE TABLE `mitre_details` ( `id` int(11) NOT NULL, `reading` double NOT NULL, `date` date NOT NULL ) – mrinmoy Jul 16 '19 at 11:09
with data too? so that i could check from my local – James Jul 16 '19 at 11:11
INSERT INTO `mitre_details` (`id`, `reading`, `date`) VALUES (1, 500, '2019-07-15'), (2, 300, '2019-07-15'), (3, 600, '2019-07-15'), (4, 800, '2019-07-15'), (5, 900, '2019-07-15'), (1, 200, '2019-07-16'), (2, 250, '2019-07-16'), (3, 400, '2019-07-16'), (4, 550, '2019-07-16'), (5, 790, '2019-07-16'); – mrinmoy Jul 16 '19 at 11:13
please tell me solution above problem – mrinmoy Jul 16 '19 at 11:39

i want to get some row values as column day wise for one month

1 Answers1