CODEWITHSUNDEEP
phpmysqlcount
ee12csvt
ee12csvt

Reputation: 17

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

Upvotes: 1

Views: 125

Answers (2)

Quassnoi
Quassnoi

Reputation: 425713

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

Upvotes: 0

DKSan
DKSan

Reputation: 4197

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.

Upvotes: 1

Related Questions