| Subject | Grouping by multiple columns |
|---|---|
| Author | m. Th. |
| Post date | 2014-02-27T09:24Z |
Hi all,
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)
D03 HOURS,
D04 HOURS,
D05 HOURS,
D06 HOURS,
D07 HOURS,
D08 HOURS,
D09 HOURS,
D10 HOURS,
D11 HOURS,
D12 HOURS,
D13 HOURS,
D14 HOURS,
D15 HOURS,
D16 HOURS,
D17 HOURS,
D18 HOURS,
D19 HOURS,
D20 HOURS,
D21 HOURS,
D22 HOURS,
D23 HOURS,
D24 HOURS,
D25 HOURS,
D26 HOURS,
D27 HOURS,
D28 HOURS,
D29 HOURS,
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?
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)
D03 HOURS,
D04 HOURS,
D05 HOURS,
D06 HOURS,
D07 HOURS,
D08 HOURS,
D09 HOURS,
D10 HOURS,
D11 HOURS,
D12 HOURS,
D13 HOURS,
D14 HOURS,
D15 HOURS,
D16 HOURS,
D17 HOURS,
D18 HOURS,
D19 HOURS,
D20 HOURS,
D21 HOURS,
D22 HOURS,
D23 HOURS,
D24 HOURS,
D25 HOURS,
D26 HOURS,
D27 HOURS,
D28 HOURS,
D29 HOURS,
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?