using sum with join

Question

I program a site and I can't get this query right.

I have a table that contain the channels

select * from `channels`

and I have another table that contain the statistics of viewing every page in the site.

To get the all visits to a specific channel I will write

SELECT SUM(ip) AS visits FROM `log` where `module` = 'channels' and mid = '15'

where 15 is equal the id of a row in the channels table.

The query that I need to write should use a join with sum to select * from channels and add an extra cell (value) to get the number of visits for every id in Channels.

From comments to answers:

The table called Channels contains columns id and name. The table called Log contains columns ip, module, mid. The log.module = 'channel'; log.mid = channel.id. Every channel id (such as '15') can get all of its visits by the query SELECT SUM(ip) AS visits FROM log where module = 'channels' and mid = '15'.

Roughly:

CREATE TABLE Channels (ID INTEGER, Name CHAR(30), PRIMARY KEY ID);
CREATE TABLE Log (IP CHAR(16), Module CHAR(10), MID INTEGER REFERENCES Channels(ID));

i don't understant join query as well , because of that i tried to make a cell with name of visits that record the visits , but it is not Accuracy , the table of log is more Accuracy — Moustafa Abdul Kareem
– Moustafa Abdul Kareem, Commented Apr 23, 2012 at 0:31

eggyal · Accepted Answer · 2012-04-23 00:45:32Z

1

Since you mention that you're unfamiliar with SQL joins, perhaps you'd find the Wikipedia article or the W3Schools lesson on the subject informative (these were the top 2 results when I searched Google for "SQL join").

Failing that, following the link about SUM() in the MySQL function reference takes one to a page on GROUP BY (Aggregate) functions, which is rather a strong hint towards the answer.

SELECT channels.*, SUM(ip) AS visits
FROM
  channels JOIN log ON (
    log.module = 'channels' AND
    log.mid = channels.id -- or whatever is the appropriate column in `channels`
  )
GROUP BY log.mid;

edited Apr 23, 2012 at 0:45

answered Apr 23, 2012 at 0:37

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan Leffler · Accepted Answer · 2012-04-23 00:45:04Z

0

You need to provide some more information on the tables to get a good answer. Specifically, you've not explicitly provided information about which column(s) in Channels join with which columns in Visits. However, with some reading between the lines, a JOIN query with GROUP BY should do the job:

SELECT c.id, COUNT(*) AS Visits
  FROM Channels AS C
  JOIN Log      AS L ON c.id = l.mid
 WHERE l.module = 'channels'
 GROUP BY c.id;

You can also move the WHERE clause into the ON condition, but the optimizer should do that automatically for you.

If you want to see all Channel records, even those with zero counts, you have to use a LEFT OUTER JOIN (abbreviated LEFT JOIN) and COUNT(x) where x is an appropriate column in the Log table:

SELECT c.id, COUNT(l.mid) AS Visits
  FROM Channels AS C
  LEFT JOIN Log AS L ON c.id = l.mid
 WHERE l.module = 'channels'
 GROUP BY c.id;

edited Apr 23, 2012 at 0:45

answered Apr 23, 2012 at 0:39

Jonathan Leffler

760k145 gold badges961 silver badges1.3k bronze badges

2 Comments

Moustafa Abdul Kareem Over a year ago

the table of the channel contain id,name and the table of log contain ip,module,mid module = channel mid = channel.id every channel id can get all of its visits by query SELECT SUM(ip) AS visits FROM log where module = 'channels' and mid = '15'

Jonathan Leffler Over a year ago

@MoustafaAbdulKareem: update the question to include the schemas of both tables. And, please do that automatically in future. It is hard to answer questions when the key information is missing (in particular, table names, relevant columns, the primary key and foreign key information).

Collectives™ on Stack Overflow

using sum with join

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related