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 ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
