0

I have a table with datetime, errorCode and integer value (Count of products with this error code...).

I need to SELECT and grouped by week(time) all distinct errorCodes with sum of integers including zeros. So in every week must be all distinct errorCodes.

Table can look like this:

2016-09-12, 0, 100
2016-09-12, 0, 10
2016-09-12, e1, 10
2016-09-21, e2, 5

And result what i want:

week, errorCode, sum
37,  0, 110
37, e1, 10
37, e2, 0
38, 0, 0
38, e1, 0
38, e2, 5

Thanks.

fbituco
  • 15
  • 5
  • 2
    You're going to need a calendar table of sorts, as your result set appears to be including data which does not appear in the source table. – Tim Biegeleisen Sep 24 '16 at 09:39
  • you will also need a table with all the error codes – Jayvee Sep 24 '16 at 09:44
  • I can get all error codes by SELECT DISTINCT table.errorCodes – fbituco Sep 24 '16 at 09:48
  • And in result table are only data from source table. week(time) and sum of integers with all error codes for every week(time). – fbituco Sep 24 '16 at 09:50
  • You need (everyone needs) some helper tables for left joins. One is created [here](http://stackoverflow.com/a/39559794) at the bottom in **Edit3** first block, table name `4kTable`. Sometimes they are helper tables that have every `date` for a decade for sales reports (again `left joins`). They are way faster than the goofy `UNION` x 100 rows that you see people put together here imho :p Helper tables are permanent, indexed, and you can use them again and again in many queries – Drew Sep 24 '16 at 10:04

1 Answers1

0

On a basic level this will show the sum of errors, including the ones summing 0, for all the weeks where there was at least one error type. The other shortcoming of this one is that the weeks are ordinal numbers in relation to the year, i.e. 0-51. If you need this to work across years and/or include all weeks then as it was pointed out, you may need to drive from a calendar and left join to this query:

SELECT week(week), e.errorCode, sum(case when e.errorCode=x.errorCode then c else 0 end) from
errorCodes e, xtest x
group by week(week), e.errorCode
Jayvee
  • 10,670
  • 3
  • 29
  • 40
  • Ok, this is what i want. And ok, I know about problem with weeks, but it is not problem now. But if I run your function, it will show weeks and error codes ok, but sum is identical for all error codes in week (It is sum of all error codes in week). – fbituco Sep 24 '16 at 17:38
  • Great. if it works for you, please accept the answer. – Jayvee Sep 24 '16 at 17:40
  • I have changed the group by to be week(week) this should add up by ordinal week/error code – Jayvee Sep 24 '16 at 17:49
  • `SELECT week(production_results.Time), production_errors.code, sum(production_results.Count) FROM production_errors, production_results GROUP BY week(production_results.Time), production_errors.code` <-- If I use this code, result in sum is not sum of values with one errorCode, but it is sum of ALL values in week... So sum is identical for all errorCodes – fbituco Sep 24 '16 at 17:59
  • Ok, I think we may need a sum(case when production_errors.code=production_results.code then production_results.Count else 0 end) – Jayvee Sep 24 '16 at 18:18