getting multiple rows & column data into a single row of a table
msinghNov 21 2011 — edited Nov 21 2011Hi,
I am newbie in mysql. And I am developing some chart for my application as I am using my sql query browser. I am getting some problem, while fetching the rows of my table. I have a table which displays simple data :
Table :
datastream
column name
checked | received_date
1 | 2011-11-16
2 | 2011-11-16
0 | 2011-11-16
1 | 2011-11-17
2 | 2011-11-17
0 | 2011-11-17
1 | 2011-11-16
2 | 2011-11-16
0 | 2011-11-16
what I need to show group by checked and received date as well. I have tried this query:
SELECT checked, count(checked), received_date FROM datastream j group by checked, received_date;
it is working fine. but what I need to do to display it into a single row i.e:
2011-11-16 | 1 = 2 | 2 = 2 | 0 = 2
where
first value is received date, second one is checked, and it's count rest of them similar to one.
I am trying this query :
SELECT checked, count(checked), received_date, group_concat(concat(checked, count(checked), received_date separator '=')) FROM datastream j group by checked, received_date order by received_date ASC;
I am using group_concat function for MySQL. but getting an error. I can be wrong, can someone help me what is the suitable option to do this. Is it possible by using java code or do I need to work on query.
Can anyone help me to fix this issue. I really appreciated if someone can correct me in this query or suggest me to do it with java code (please share code snippet for the same).
Thanks in advance.
Manveer
Edited by: msingh on Nov 21, 2011 10:52 AM