CODEWITHSUNDEEP
mysqlsql
user3918313
user3918313

Reputation: 19

sql - select all values id's and count how many time they appear within the last month

SELECT i.name, x.total FROM Item i, 
(SELECT Item_id, COUNT(Item_id) as total FROM Tracking 
WHERE date > DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY Item_id)x 
WHERE i.id = x.Item_id

what I want is to count how many times a resource is used in the last month example- (item 1, 0), (item 2, 4), (item 3, 2) what I get is only the items that have a value, so item 1 will not show and any other items that don't appear in the last month

Upvotes: 1

Views: 524

Answers (2)

Mureinik
Mureinik

Reputation: 312066

SELECT     i.name, x.total 
FROM       Item i
LEFT JOIN  (SELECT   Item_id, COUNT(Item_id) as total 
            FROM     Tracking 
            WHERE    date > DATE_SUB(NOW(), INTERVAL 1 MONTH) 
            GROUP BY Item_id) x ON i.id = x.Item_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270733

If you want all items, regardless of whether they had any appearances, then use conditional aggregation instead of a where clause:

SELECT Item_id,
       SUM(date > DATE_SUB(NOW(), INTERVAL 1 MONTH)) as total
FROM Tracking 
GROUP BY Item_id;

Upvotes: 1

Related Questions