|Subject||Re: [firebird-support] Grouping by multiple columns|
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,This question was also posted to stackoverflow:
> 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?