Page 1 of 1

PHP & MySQL - Total and Subtotals

Posted: Mon May 01, 2006 10:18 pm
by mratomika
So I have looked everywhere on Google, and in a ton of forums for the past
two days and have had no success with what I am trying to code, so ANY
assistance would be appreciated. Thank you.

I have to Tables in a MySQL Database.

One is called weekly ; it holds information per employee.

Code: Select all

weeklyId, employeeID, departmentID, reportingPeriod, hoursWorked, hoursOut, vacationHours

1 - 1 - 1 - unixtimestamp - 40 - 10 - 25
2 - 2 - 1 - unixtimestamp - 40 - 10 - 25
3 - 4 - 2 - unixtimestamp - 12- 35 - 15
4 - 3 - 3 -  unixtimestamp - 14 - 95 - 0
The other table called departmentList; it holds each department name.

Code: Select all

departmentID, departmentName

1 - Womens Underwear
2 - Sports Equipment
3 - Kitchenware
4 - Toys
I want to find a complete Total of each column per week: hoursWorked, hoursOut, vacationHours.
That is easy enough, just pull everything from the database, and as I loop it add it all
together.

I can find grand totals for each individual . . . even easier.

Yet, here is where I can not figure out any logic, listing each Department once, and Subtotals
for each one . . . I am at a complete and total loss as to the logic. I have tried putting into an
array and sorting it out properly, I have tried mildly comlpex SQL queries, with no avail . . .

For Example:

Code: Select all

Womens Underwear: 80 - 20 - 50
Sports Equipment: 12- 35 - 15
and on and on . . .

If I need to post anything else, please just say so . . .
Thank you thank you thank you.

-mr atomika


Posted: Mon May 01, 2006 10:38 pm
by feyd
seems like a GROUP BY mixed with a JOIN would work just fine. Figure out each separately. Combining them isn't all that hard after that.

Posted: Tue May 02, 2006 2:13 pm
by timvw
If i'm not mistaken mysql has a nice 'WITH ROLLUP' extension for the GROUP BY clause...

Posted: Wed May 03, 2006 1:25 am
by raghavan20
timvw wrote:If i'm not mistaken mysql has a nice 'WITH ROLLUP' extension for the GROUP BY clause...
What is a rollup extension and I have never heard of it.


To the original question, make a normal join / inner join using deparment id and then if you add a clause, group by department id at the end, it will work.

Worked!

Posted: Wed May 03, 2006 2:12 am
by mratomika
Worked like a charm . . .
Maybe I need to re-read my SQL commands!

Thanks a ton!

Posted: Wed May 03, 2006 6:08 am
by timvw
WITH ROLLUP modifier for the GROUP BY clause.