0

I am using this query for weekly reporting but can not found a way like this

week_number | week_startdate | organization_1 | organization_2
---------------------------------------------------------------
1           | 2013-01--05    |count(date) like 4,24,etc_ | count(date) like 4,24,etc_

SQL:

SELECT WEEK(signed_date) AS week_name, signed_date AS Week_Starting,
       YEAR(signed_date), WEEK(signed_date), COUNT(*)
FROM business
WHERE YEAR(signed_date) = YEAR(CURDATE())
GROUP BY CONCAT(YEAR(signed_date), '/', WEEK(signed_date))
ORDER BY YEAR(signed_date), WEEK(signed_date

SAMPLE DATA:

signed_date | organization_id
01-01-2013 | 1
02-01-2013 | 1
03-01-2013 | 2

In 1 week organization_1 have 2 signed & organization_2 has 1 signed.

Syed Osama
  • 133
  • 1
  • 1
  • 8

1 Answers1

2

You should use case within count or sum:

SELECT WEEK(signed_date) AS week_name, signed_date AS Week_Starting,
       YEAR(signed_date), WEEK(signed_date),
SUM(CASE  WHEN organization_id=1 THEN 1 ELSE 0 END) as organization_1,
SUM(CASE WHEN organization_id=2 THEN 1 ELSE 0 END) as organization_2
FROM business
WHERE YEAR(signed_date) = YEAR(CURDATE())
GROUP BY CONCAT(YEAR(signed_date), '/', WEEK(signed_date))
ORDER BY YEAR(signed_date), WEEK(signed_date);

http://sqlfiddle.com/#!2/587ad/3

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • I have another question related to your answer. Is any option to show also those week which has no transaction or no related data. Means 'organization 1 ' and 'organization 2 ' no signed data in any week. Can we show that data by 0 | 0 | 0 .. etc. – Syed Osama Mar 03 '13 at 16:18
  • 1
    @SyedOsama MySQL sadly lacks the generate_series of PostgreSQL but that still is possible. Check out how it's done here: http://stackoverflow.com/a/6871220/2115135 Your week generation will be something like this: http://sqlfiddle.com/#!2/ea6eb2/6 then you have to left join that table with your business – Jakub Kania Mar 03 '13 at 18:13