MySQL Group by week num w/ multiple date column

Question

I have a table with columns similar to below , but with about 30 date columns and 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 what I need to do is get a query with output similar to

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

etc

My query is

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 am getting is the forcast_date counts repeated in each column, i.e.

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

Can any one please tell me the best way to formulate the query to get what I need??

Thanks

What data types are the columns `forcast_date` and `actual_date`? `DATE`, `VARCHAR` or something else? What should your desired output `count_forcast` and `count_actual` represent? The amount of different values in those columns? — Bjoern, Jul 10 '13 at 06:20
Something you should also consider is whether you want a row to represent a week even when no forcast_dates fall in that week. By grouping by weekofyear(forcast_date), your result set will only include weeks that have at least one forcast_date record in that week. — Zec, Jul 10 '13 at 06:31
@Bjoern all teh dates are DATETIME and the count_ columns are the number of times an id falls within each week — pwatt01, Jul 10 '13 at 06:52
@Zec if there are no dates within a week, having that date appear as a row would be preferable, but not necessary. however, if there is a actual_date where there is no forecast_date, i would need to see the count of actual_date — pwatt01, Jul 10 '13 at 06:55
You guys are ahead of me. I have no idea how the data set relates to the result set!!! — Strawberry, Jul 10 '13 at 07:02
I admit to limited understanding, but your case sounds like it could be aided with a pivot table. http://stackoverflow.com/a/7675121/2400222 — Zec, Jul 10 '13 at 07:12

Edper · Answer 1 · 2013-07-10T07:18:35.093

0

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's my Fiddle Demo

edited Jul 10 '13 at 07:18

answered Jul 10 '13 at 06:29

Edper

9,144
1
27
46

1

This is essentially the same query as the OP has provided and will return similar results. The problem is not with the undeclared grouping field "week_num" (No doubt OP's ACTUAL query uses this alias), it's that both the count values are the same. – Zec Jul 10 '13 at 06:33
You maybe right @Zec I didn't see the implied grouping alias that he did not declare. – Edper Jul 10 '13 at 06:43
ah yes, apologies for that.. i had a column WEEKOFYEAR(forcast_date) as week_num. I have editied the OP to match – pwatt01 Jul 10 '13 at 06:50
@Edper thanks Edper, but no luck with that one. I think I may need to use a calendar table to get the week numbers or something... i will let you know how I go. If you have any inspirations, please let me know – pwatt01 Jul 11 '13 at 01:49
@pwatt01 what do you mean by no luck, does it mean you have tested it and it does not work? and yes in what way it did not work? Have check my fiddle demo? And yes if I am another inspiration I will post it also. – Edper Jul 11 '13 at 01:57
@Edper sorry about the lack of detail.. i tested it on the data and the counts for actual were inaccurate. If i cahnged the line 'SELECT weekofyear(forecast_date) as WeekInYear' from the 'actualTable' select to have the actual_date i get an accurate count of the actual date, but the forcast is a bit out and stops when the actual date stops – pwatt01 Jul 11 '13 at 03:16

score 0 · Answer 2 · answered Jul 10 '13 at 06:53

0

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 have to write about 30 (your date columns) left join, and the requirement is that your first date column shouldn'd have empty week (with a count of 0) or the joins will miss.

answered Jul 10 '13 at 06:53

mauretto

3,183
3
27
28

That one occured to me, but as i will be needing to have 10+ count columns, I was hoping to avoid having 10 subqueries if possible.. if not possible, then there is nothing i can do, but I was hoping for an better way. Also, the above solution will only show weeks where there is a forecast date – pwatt01 Jul 10 '13 at 06:59
@mauretto Can you explain the GROUP BY with t2.count_actual at the end? It seems strange to group by a count instead of a date item. The query does work for me but I wonder if it would have a problem if two weeks had the same count_actual. – Michael K Apr 18 '17 at 15:49
@pwatt01 If you had a utility table of weeks, then if you used that in a JOIN, that would show a row for every week whether or not there is data for that week. It may seem odd needing to have a table of constants but it works pretty well. https://forums.mysql.com/read.php?10,403351,403351#msg-403351 – Michael K Apr 18 '17 at 16:12

score 0 · Accepted Answer · answered Jul 23 '13 at 04:34

Just in case someone else comes along with the same question:

Instead of trying to use some amazing query, I ended up creating an array of date_columns_names and a loop in the program that was calling this query, and for each date_column_name, performing teh asme query. It is a bit slower, but it does work

MySQL Group by week num w/ multiple date column

3 Answers3

Linked