On 27-2-2014 10:24, m. Th. wrote:

> I have the following table which holds the working hours for each worker
> and for each workplace:
>
> CREATE TABLE WAGES_HOURS (
> ID LONGID NOT NULL, -- Table's Abstract Primary Key
> WORKERID LONGID NOT NULL, -- The FK for the Worker
> MONTHID ID NOT NULL, -- The month: 1 - January, 2 - Feb., ..., 12 - Dec.
> PLACEID LONGID NOT NULL, -- The FK for the Workplace
> D01 HOURS, -- how many hours worker worked in the workplace in the
> Day 1. A value of 0 means no work.
> D02 HOURS, -- the same for the Day 2 ...and so on till the last day
> of month (D31)

...

> D30 HOURS,
> D31 HOURS);
>
> From this data we want to show one or two report(s) for a concrete
> month (entered in GUI) which will show:
>
> - how many workers worked in each day in each workplace
> - (optional) the sum of hours for each worker in each day in each workplace
>
> I cannot figure how to do it in the best way - till now I have 31
> queries which do the grouping for each day - something like this:
>
> select COUNT(workerid), placeid from wages_hours where monthid=:myMonth
> and d01>0 group by placeid;
>
> ...but I feel tha it is possible a better way.
>
> Does someone know it?

This question was also posted to stackoverflow:

http://stackoverflow.com/questions/22064313/how-to-do-a-sql-grouping-by-multiple-columns-including-days

Mark
--
Mark Rotteveel