MySQL help, counting information on last records

I have two tables, one holds unique serial numbers of items (items) and the other holds status changes and other information for these items (details).

The Tables are set up as follows:

Item
----
itemID
itemName
itemDate

details
-------
detID
itemID
modlvl
status
detDate

All items have at least one record in the details table, but over time the status has changed or the modification level has changed (Both of these are identified by numbers which are held in other appropriate tables) and a new record is created each time the status/modlvl changes.

I want to display a table on my webpage using php that identifies the different mod levels of the items and shows a count of each of the current status of the items.

EDIT

This is an example of the data in the tables and what I want to achieve.

The current Mod Levels range from 1 to 3.

Status representations are:

1   In Use
2   In Store
3   Being repaired
4   In Transit
5   For Disposal
6   Disposed
7   Lost

Item

itemID  itemName OrigMod itemDate    
1000    PSU      1       2009-10-01 22:12:12
1001    PSU      1       2009-10-01 22:12:12
1002    PSU      1       2009-10-01 22:12:12
1003    PSU      1       2009-10-01 22:12:12
1004    PSU      1       2009-10-01 22:12:12
1005    PSU      1       2009-10-01 22:12:12
1006    PSU      1       2009-10-01 22:12:12
1007    PSU      1       2009-10-01 22:12:12
1008    PSU      1       2009-10-01 22:12:12
1009    PSU      1       2009-10-01 22:12:12
1010    PSU      1       2009-10-01 22:12:12

Details

detID   itemID  modlvl  detDate     status

1       1000    1       2009-10-01   1
2       1001    1       2009-10-01   1
3       1002    1       2009-10-01   1
4       1003    1       2009-10-01   1 
5       1004    1       2009-10-01   1
6       1005    1       2009-10-01   1
7       1006    1       2009-10-01   1
8       1007    1       2009-10-01   1
9       1008    1       2009-10-01   1
10     1009    1       2009-10-01   1
11     1010   1 2009-10-01  1
12     1001   1 2010-02-01  2
13     1001   1 2010-02-03  4
14     1001   1 2010-03-01  3
15     1000   1开发者_如何学运维 2010-03-14  2
16     1001   2 2010-04-01  4
17     1006   1 2010-04-01  2
18     1001   2 2010-04-03  2
19     1006   1 2010-04-14  4
20     1006   1 2010-05-01  5
21     1002   1 2010-05-02  2
22     1003   1 2010-05-10  2
23     1010   1 2010-06-01  2
24     1006   1 2010-06-18  6
25     1010   1 2010-07-01  7
26     1007   1 2010-07-02  2
27     1007   1 2010-07-04  4
28     1003   1 2010-07-10  2
29     1007   1 2010-07-11  3
30     1007   2 2010-07-12  4
31     1007   2 2010-07-15  2
32     1001   2 2010-08-31  1
33     1001   2 2010-09-10  2
34     1001   2 2010-10-01  4
35     1008   1 2010-10-01  2
36     1001   2     2010-10-05  3
37     1008   1 2010-10-05  4
38     1008   1 2010-10-10  3
39     1001   3 2010-10-20  4
40     1001   3 2010-10-25  2

Using the tables above I want to get this result

MoLvl Use Store Repd Transit Displ Dispd Lost Total
1     3   3     1    0       0     1     1    9
2     0   1     0    0       0     0     0    1
3     0   1     0    0       0     0     0    1
Total 3   5     1    0       0     1     1   11

you could try this query:

SELECT Item.itemID, Item.itemName, max(details.modlvl), max(details.status) 
FROM Item, details
WHERE Item.itemID = details.itemID
GROUP BY Item.itemID, Item.itemName

It is untestet, but should work quite well.

SELECT  m.modlvl,
        COALESCE(SUM(status = 1), 0) AS use,
        COALESCE(SUM(status = 2), 0) AS store,
        …,
        COALESCE(COUNT(detId)) AS total
FROM    (
        SELECT  1 AS modlvl
        UNION ALL
        SELECT  2 AS modlvl
        UNION ALL
        SELECT  3 AS modlvl
        ) m
LEFT JOIN
        details d
ON      d.modlvl = m.modlvl
GROUP BY
        m.modlvl WITH ROLLUP