count & multiple group by

[ryangsh]

I'm trying to consolidate inventory records in db. (I'm displaying the result in a table form but for this post I'm putting it simple)

table_name= PC:
department - msoffice
IT - 2000
IT - 2002
IT - 2000
SNM - 2000
SNM - 2003
Finance - 2003
-----------------------------------

expected output:
msoffice - dept - qty
2000 - IT - 2
2000 - SNM - 1
2002 - IT - 1
2003 - Finance - 1
2003 - SNM - 1

Group total by msoffice:
2000 = 3 (2+1)
2002 = 1
2003 = 3 (2+2)

Grand Total : 7
----------------------------------

i have tried this method in 2 different queries that give me 2 outputs. 1 displays the Group Total and the other one displays the grouped result.

Query#1:

$qry=mysql_query("SELECT `msoffice`,`department`, COUNT(`msoffice`) as 'qty', COUNT(`department`) as 'dept' FROM pc GROUP BY `department`");
while ($result=mysql_fetch_array($qry)){
echo $result['msoffice']." - " .$result['qty'] ."<BR>";
}
 

output for query #1:
2000 - 3
2002 - 1
2003 - 3
--------------------------------------…
Query #2:

$qry2=mysql_query("SELECT `msoffice`,`department`, COUNT( `department`) as 'qty' FROM pc GROUP BY `msoffice`,`department`");
while ($result2=mysql_fetch_array($qry2)){
echo $result2['department']." - ' . $result2['msoffice'] . " - " .$result2['qty']. "<BR>";
}
 

output for query #2:
IT - 2000 - 2
SNM - 2000 - 1
IT - 2002 - 1
Finance - 2003 - 1
SNM - 2003 - 1
--------------------------------------…

problem:
1) ***MAIN Issue*** I don't know how to get the group total by msoffice. And the overall total i think i can use the row count (is there any better way?)
2) Can the 2 query be combined as 1? If can what is the best method (guide me with example)?

You help is greatly appreciated.