CODEWITHSUNDEEP
phpmysql
Joe Thomas
Joe Thomas

Reputation: 23

Retrieve SUM of hits from another table

My query is:

SELECT * FROM sites WHERE status = '1' ORDER BY id DESC

From here, inside my while loop I have the all the urls that are active. I want to check these urls in another table 'hits', and get the SUM of the 'stats' for each site that is active.

How can this be accomplished? Is this done with a JOIN statement?

table structures

sites

id  int(11) unsigned    NO  PRI NULL    auto_increment
url varchar(100)    NO  UNI     
status  int(11) YES     1   
added   timestamp   NO      CURRENT_TIMESTAMP   on update CURRENT_TIMESTAMP

hits

id  int(11) unsigned    NO  PRI NULL    auto_increment
domain  varchar(30) NO          
stats   int(11) YES     NULL    
added   timestamp   NO      CURRENT_TIMESTAMP

Upvotes: 2

Views: 74

Answers (2)

Simon
Simon

Reputation: 1207

Rolando's on the right track, but to sum the stats you'll want something more like this:

select sites.id, sites.url, sum(hits.stats)
from sites
join hits on sites.url = hits.domain
where sites.status = 1
group by sites.id, sites.url

If you want sites that are active but have no hits, make the join a left join.

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

SELECT
    A.id,COUNT(1) site_hits
FROM
    (SELECT id FROM sites WHERE status='1' ORDER BY id DESC) A
    INNER JOIN
    hits B ON A.id = B.site_id
GROUP BY
    A.id
;

CAVEAT #1 for this query : You will need a good index for this one. I recommend the following:

ALTER TABLE sites ADD INDEX status_id_ndx (status,id);

CAVEAT #2 for this query : You could make the index more efficient.

With status being int(11), you could accommodate 2147483647 possible status values. I don't think you have that many. If the highest value for status < 256, you could change it as follows:

ALTER TABLE sites MODIFY COLUMN status int unsigned not null;

Result? Smaller table, small index, and faster access.

You need something like this. I do not see any correlation between sites and hits in your question. You need the id of the site to be stored in hits table. If you are trying to join by domain against url, you will need to express the join that way. That part would seem messy. It would resemble something like this:

SELECT
    A.id,COUNT(1) site_hits
FROM
    sites A,hits B
WHERE
    LOCATE(B.domain,A.url)
GROUP BY
    A.id
;

Upvotes: 3

Related Questions