Geek Answers Handbook

MySQL group by weeks with multiple date columns

I have a table with columns similar below but with approximately 30 date columns and over 500 records

id | forcast_date | actual_date 1 10/01/2013 12/01/2013 2 03/01/2013 06/01/2013 3 05/01/2013 05/01/2013 4 10/01/2013 09/01/2013

and I need to make a query with a result similar to

 week_no | count_forcast | count_actual 1 4 6 2 5 7 3 2 1

etc.

My request

 SELECT weekofyear(forcast_date) as week_num, COUNT(forcast_date) AS count_forcast , COUNT(actual_date) AS count_actual FROM table GROUP BY week_num

but what i get is forcast_date numbers repeating in each column i.e.

 week_no | count_forcast | count_actual 1 4 4 2 5 5 3 2 2

Can someone tell me the best way to formulate a request in order to get what I need?

thanks

+1
source share
3 answers

Just in case, someone else comes up with the same question:

Instead of trying to use some awesome query, I created an array of date_columns_names and a loop in the program that called this query, and for each name date_column_name, executing the asme query. It's a little slower but it works

0
source

Try:

 SELECT WeekInYear, ForecastCount, ActualCount FROM ( SELECT A.WeekInYear, A.ForecastCount, B.ActualCount FROM ( SELECT weekofyear(forecast_date) as WeekInYear, COUNT(forecast_date) as ForecastCount, 0 as ActualCount FROM TableWeeks GROUP BY weekofyear(forecast_date) ) A INNER JOIN ( SELECT * FROM ( SELECT weekofyear(forecast_date) as WeekInYear, 0 as ForecastCount, COUNT(actual_date) as ActualCount FROM TableWeeks GROUP BY weekofyear(actual_date) ) ActualTable ) B ON A.WeekInYear = B.WeekInYear) AllTable GROUP BY WeekInYear;

Here is my Demo Screenshot

0
source

to try:

 SELECT weekofyear(forcast_date) AS week_forcast, COUNT(forcast_date) AS count_forcast, t2.count_actual FROM t t1 LEFT JOIN ( SELECT weekofyear(actual_date) AS week_actual, COUNT(forcast_date) AS count_actual FROM t GROUP BY weekOfYear(actual_date) ) AS t2 ON weekofyear(forcast_date)=week_actual GROUP BY weekofyear(forcast_date), t2.count_actual

sqlFiddle

You need to write about 30 (date columns) of the left join, and the requirement is that your first date column should not have an empty week (with the number 0) or there will be no joins.

0
source

More articles:


All Articles