Items and sum

[chrischen]

I have a table of ITEMS, and a table of SUMS.

 

I would like to display specific items from table ITEMS and also have the sum of specific column in table SUMS that has a foreign key pointing to that item in table ITEMS.

 

So for example lets say ITEMS was a table of websites and SUMS is a table of web traffic where each row is a day and each day has the amount of visitors.

 

Items (Websites)

 

Site_id | Site name | site category

1 | yahoo.com | 1

2 | msn.com | 1

3 | google.com | 2

4 | youtube.com | 3

5 | yahoovideo.com | 3

 

Sums (traffic)

site_id | day | visitors

1 | 1/1/2009 | 851075

1 | 1/2/2009 | 172083

2 | 1/1/2009 | 1750207

2 | 1/2/2009 | 2357

3 | 1/1/2009 | 123

 

I would like to display a list of sites with category 1 and also display the sum of visitors for that site in one query. Is that possible?

 

I tried querying SUMS table and left joining ITEMS table and used group by, but if there are no rows in SUMS for a particular site then it is not displayed. I would like the site to be displayed even if there are no rows in SUMS pointing to it.

 

Thanks in advance.

[aschk]

I'll break this down:

 

Total Visitors (for all days) for site_id 1

SELECT site_id
      , SUM(visitors)
FROM Sums
WHERE site_id = 1
GROUP BY site_id

 

Linking the above to a "Site Name"

SELECT i.site_id
      , i.site_name
      , SUM(s.visitors)
FROM items i
LEFT JOIN Sums s ON i.site_id = s.site_id
GROUP BY i.site_id, i.site_name

 

P.S. you never supplied a SQL query for us to start working with, so I can only really guess at what you were doing before.

 

P.P.S it might also be worth me pointing out you are storing your dates in a non-standard format. Use MySQL DATE datatype please instead.

[aschk]

For anyone else interested I have generated a SQL schema for testing:

 

CREATE TABLE items (
site_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
site_name VARCHAR(255) NOT NULL,
site_category INT UNSIGNED NOT NULL
);

INSERT INTO items(site_name, site_category)
VALUES('yahoo.com',1)
,('msn.com',1)
,('google.com',2)
,('youtube.com',3)
,('yahoovideo.com',3);

CREATE TABLE Sums(
site_id INT UNSIGNED NOT NULL,
`day` DATE NOT NULL,
visitors INT UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO Sums(site_id, `day`, visitors)
VALUES(1,'2009-01-01',851075)
,(1,'2009-01-02',172083)
,(2,'2009-01-01',1750207)
,(2,'2009-01-02',2357)
,(3,'2009-01-01',123);