CODEWITHSUNDEEP
mysqlmysql-error-1111
qwera
qwera

Reputation: 185

mysql group concat

how can I group concat? here is my query

select t.date, group_concat(count(*)) from table1 t
group by t.date

but it returns error "Invalid use of group function"

If I use query like this

select t.date, count(*) from table1 t
group by t.date

then it returns following output but I want to group_concat this output

2011-01-01  100
2011-01-02  97
2011-01-03  105

Upvotes: 1

Views: 1606

Answers (3)

Martin Smith
Martin Smith

Reputation: 452977

SELECT GROUP_CONCAT(`cnt` ORDER BY `date`) 
FROM (
    SELECT t.`date`, COUNT(*) AS `cnt`
    FROM `table1` t
    GROUP BY t.`date`
) d

Upvotes: 1

Vincent Savard
Vincent Savard

Reputation: 35907

You want something like this :

SELECT GROUP_CONCAT(n) groupN
FROM (SELECT COUNT(*) n
      FROM table1
      GROUP BY date) tmp

But that's not the role of the RDBMS! Cosmetic is the role of your application language (PHP, Python, Ruby, whatever), your query should only select your data, period. Therefore, GROUP_CONCAT is not the solution in this case.

Upvotes: 0

Max Kielland
Max Kielland

Reputation: 5841

Do you want to count the number of date rows group by date?

I use this statement to count number of invoice items per date in a specific month.

SELECT date(i.Date), count(*) cnt
FROM invoice i
WHERE MONTH(i.Date) = 3
GROUP BY date(i.Date)

This will group all dates that are the same. Is this what you meant?

I use GROUP_CONCAT for subqueries returning more than one row.

* EDIT * OUPS, saw that my suggestion was the same as your already tried. Then I don't understand what you are looking for. Can you please show an example of desired result?

Upvotes: 0

Related Questions