2
Table reports :
id       (int)
catid    (int)
pub_date (datetime)

Table categories :
id           (int)
parent_id    (int)

I want to get the report results with count(id) 's and group by catid's parent_id.

For example :

 Date           Count(parent_id = 1)    Count(parent_id = 2)  Count(parent_id = 3)
 
2014-02-24            2                            5                      8

We should have get the results in only a line per day.

Thanks too much in advance.

vural
  • 381
  • 2
  • 11
  • possible duplicate of [How to create a pivot query in sql server without aggregate function](http://stackoverflow.com/questions/14618316/how-to-create-a-pivot-query-in-sql-server-without-aggregate-function) – Jordan.J.D Mar 03 '14 at 14:59

1 Answers1

0

How about using the below query (SQL Fiddle):

SELECT SUM(CASE WHEN r.id = 1 THEN 1 ELSE 0 END) AS PID_1,
       SUM(CASE WHEN r.id = 2 THEN 1 ELSE 0 END) AS PID_2,
       SUM(CASE WHEN r.id = 3 THEN 1 ELSE 0 END) AS PID_3
FROM reports AS r
INNER JOIN categories AS c ON c.parent_id = r.id
GROUP BY r.pub_date
Linger
  • 14,942
  • 23
  • 52
  • 79
  • the right database structure is here (http://sqlfiddle.com/#!2/178ed) thanks for your help. – vural Mar 03 '14 at 15:09
  • ***@vural***, how about the following [**SQL Fiddle**](http://sqlfiddle.com/#!2/178ed/7/0)? Is this what you are looking for? FYI, in your SQL fiddle the only date that `categories` is linking to in `reports` is **2014-02-24** so you only get one record returned. If there were more dates that `categories` is linking to then you would get more records, one for each date. Also, there is no `0` id in reports. – Linger Mar 03 '14 at 15:43
  • I have lots of datas in my reports table. So I can not use the query. Reports' date's are different from each other and their forms are like datetime (Y-m-d H:i:s) in db. I clearly want get the result in order of reports' cat_id's parent_id. Thank your interest. – vural Mar 03 '14 at 18:20
  • ***@vural***, I am sorry but I don't fully follow you. Can you update your question showing sample data in both tables? Then, show exactly what the output would look like. – Linger Mar 03 '14 at 18:24