how we fetch the data calendar wise data. like november month day wise

Unfortunately I not familiar with my sql query syntax. If at all it helps here is SQL Server solution:

select [IdUser]
,sum(case when day([msg_received]) = 1 then [msg_count] else 0 end) [Day 1]
,sum(case when day([msg_received]) = 2 then [msg_count] else 0 end) [Day 2]
,sum(case when day([msg_received]) = 3 then [msg_count] else 0 end) [Day 3]
,sum(case when day([msg_received]) = 4 then [msg_count] else 0 end) [Day 4]
,sum(case when day([msg_received]) = 5 then [msg_count] else 0 end) [Day 5]
,sum(case when day([msg_received]) = 6 then [msg_count] else 0 end) [Day 6]
,sum(case when day([msg_received]) = 7 then [msg_count] else 0 end) [Day 7]
,sum(case when day([msg_received]) = 8 then [msg_count] else 0 end) [Day 8]
,sum(case when day([msg_received]) = 9 then [msg_count] else 0 end) [Day 9]
,sum(case when day([msg_received]) = 10 then [msg_count] else 0 end) [Day 10]
,sum(case when day([msg_received]) = 11 then [msg_count] else 0 end) [Day 11]
,sum(case when day([msg_received]) = 12 then [msg_count] else 0 end) [Day 12]
,sum(case when day([msg_received]) = 13 then [msg_count] else 0 end) [Day 13]
,sum(case when day([msg_received]) = 14 then [msg_count] else 0 end) [Day 14]
,sum(case when day([msg_received]) = 15 then [msg_count] else 0 end) [Day 15]
,sum(case when day([msg_received]) = 16 then [msg_count] else 0 end) [Day 16]
,sum(case when day([msg_received]) = 17 then [msg_count] else 0 end) [Day 17]
,sum(case when day([msg_received]) = 18 then [msg_count] else 0 end) [Day 18]
,sum(case when day([msg_received]) = 19 then [msg_count] else 0 end) [Day 19]
,sum(case when day([msg_received]) = 20 then [msg_count] else 0 end) [Day 20]
,sum(case when day([msg_received]) = 21 then [msg_count] else 0 end) [Day 21]
,sum(case when day([msg_received]) = 22 then [msg_count] else 0 end) [Day 22]
,sum(case when day([msg_received]) = 23 then [msg_count] else 0 end) [Day 23]
,sum(case when day([msg_received]) = 24 then [msg_count] else 0 end) [Day 24]
,sum(case when day([msg_received]) = 25 then [msg_count] else 0 end) [Day 25]
,sum(case when day([msg_received]) = 26 then [msg_count] else 0 end) [Day 26]
,sum(case when day([msg_received]) = 27 then [msg_count] else 0 end) [Day 27]
,sum(case when day([msg_received]) = 28 then [msg_count] else 0 end) [Day 28]
,sum(case when day([msg_received]) = 29 then [msg_count] else 0 end) [Day 29]
,sum(case when day([msg_received]) = 30 then [msg_count] else 0 end) [Day 30]
,sum(case when day([msg_received]) = 31 then [msg_count] else 0 end) [Day 31]
from [dbo].[mdr]
group by [IdUser]
order by [IdUser]

Notice that you can hide irrelevant columns in front-end. For example column [Day 31] for November.

Hope you get the point.

Or check for the PIVOT TABLE syntax in MySQL.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke4y ago
Accepted answer
Unfortunately I not familiar with my sql query syntax. If at all it helps here is SQL Server solution:
select [IdUser]
  ,sum(case when day([msg_received]) =  1 then [msg_count] else 0 end) [Day  1]
  ,sum(case when day([msg_received]) =  2 then [msg_count] else 0 end) [Day  2]
  ,sum(case when day([msg_received]) =  3 then [msg_count] else 0 end) [Day  3]
  ,sum(case when day([msg_received]) =  4 then [msg_count] else 0 end) [Day  4]
  ,sum(case when day([msg_received]) =  5 then [msg_count] else 0 end) [Day  5]
  ,sum(case when day([msg_received]) =  6 then [msg_count] else 0 end) [Day  6]
  ,sum(case when day([msg_received]) =  7 then [msg_count] else 0 end) [Day  7]
  ,sum(case when day([msg_received]) =  8 then [msg_count] else 0 end) [Day  8]
  ,sum(case when day([msg_received]) =  9 then [msg_count] else 0 end) [Day  9]
  ,sum(case when day([msg_received]) = 10 then [msg_count] else 0 end) [Day 10]
  ,sum(case when day([msg_received]) = 11 then [msg_count] else 0 end) [Day 11]
  ,sum(case when day([msg_received]) = 12 then [msg_count] else 0 end) [Day 12]
  ,sum(case when day([msg_received]) = 13 then [msg_count] else 0 end) [Day 13]
  ,sum(case when day([msg_received]) = 14 then [msg_count] else 0 end) [Day 14]
  ,sum(case when day([msg_received]) = 15 then [msg_count] else 0 end) [Day 15]
  ,sum(case when day([msg_received]) = 16 then [msg_count] else 0 end) [Day 16]
  ,sum(case when day([msg_received]) = 17 then [msg_count] else 0 end) [Day 17]
  ,sum(case when day([msg_received]) = 18 then [msg_count] else 0 end) [Day 18]
  ,sum(case when day([msg_received]) = 19 then [msg_count] else 0 end) [Day 19]
  ,sum(case when day([msg_received]) = 20 then [msg_count] else 0 end) [Day 20]
  ,sum(case when day([msg_received]) = 21 then [msg_count] else 0 end) [Day 21]
  ,sum(case when day([msg_received]) = 22 then [msg_count] else 0 end) [Day 22]
  ,sum(case when day([msg_received]) = 23 then [msg_count] else 0 end) [Day 23]
  ,sum(case when day([msg_received]) = 24 then [msg_count] else 0 end) [Day 24]
  ,sum(case when day([msg_received]) = 25 then [msg_count] else 0 end) [Day 25]
  ,sum(case when day([msg_received]) = 26 then [msg_count] else 0 end) [Day 26]
  ,sum(case when day([msg_received]) = 27 then [msg_count] else 0 end) [Day 27]
  ,sum(case when day([msg_received]) = 28 then [msg_count] else 0 end) [Day 28]
  ,sum(case when day([msg_received]) = 29 then [msg_count] else 0 end) [Day 29]
  ,sum(case when day([msg_received]) = 30 then [msg_count] else 0 end) [Day 30]
  ,sum(case when day([msg_received]) = 31 then [msg_count] else 0 end) [Day 31]
from [dbo].[mdr]
group by [IdUser]
order by [IdUser]
Notice that you can hide irrelevant columns in front-end. For example column [Day 31] for November.

Hope you get the point.
Or check for the PIVOT TABLE syntax in MySQL.
0