PHP & MySQL - Total and Subtotals

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mratomika
Forum Newbie
Posts: 2
Joined: Mon May 01, 2006 9:55 pm

PHP & MySQL - Total and Subtotals

Post 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

User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

If i'm not mistaken mysql has a nice 'WITH ROLLUP' extension for the GROUP BY clause...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
mratomika
Forum Newbie
Posts: 2
Joined: Mon May 01, 2006 9:55 pm

Worked!

Post by mratomika »

Worked like a charm . . .
Maybe I need to re-read my SQL commands!

Thanks a ton!
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

WITH ROLLUP modifier for the GROUP BY clause.
Post Reply