sql multi count from different different table

Question

HOW TO Get all the leads and hits of a referer?

Table : hits_log

+-----------+----------+
| topic     | referer  |
+-----------+----------+
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | yyyyyy   |
| topic0614 | yyyyyy   |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
+-----------+----------+

Table : leads_log

+-----------+----------+
| topic     | referer  |
+-----------+----------+
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0615 | yyyyyy   |
| topic0614 | xxxxxxxx |
| topic0615 | zzzzz    |
| topic0614 | yyyyyy   |
+-----------+----------+

I want the result like this If search with topic topic0614

+-----------+----------+------------+
| referer   | hits     | leads      |
+-----------+----------+------------+
| xxxxxxxx  | 4        | 4          |
| yyyyyy    | 2        | 1          |
+-----------+----------+------------+

i have tried

SELECT h.referer, COUNT(h.referer)  as hits, COUNT(l.referer)  as leads FROM `hits_log` h ,`leads_log` l
WHERE h.topic='topic0614' and h.referer=l.referer
GROUP BY h.referer

but it didn't work

can any one help me out? Thank You.

Why does xxxxxxxx have 5 leads in your sample output? I only see 4 in the leads_log table. And why is yyyyyy only 1, there are 2 in the leads_log table. — Barmar
– Barmar, Commented Jul 5, 2014 at 12:41
i have no of hits from hit_log and no of leads from leads log storing independently so it is like that — Lucifer
– Lucifer, Commented Jul 5, 2014 at 13:08
But xxxxxxxx is only in 4 rows of hits_log, why do you have 5 in the results? — Barmar
– Barmar, Commented Jul 5, 2014 at 13:09
When counting in leads_log, you don't say to only count topic0614. So my answer counts all matching referers. — Barmar
– Barmar, Commented Jul 5, 2014 at 13:11

Barmar · Accepted Answer · 2014-07-05 13:23:19Z

4

You need to group each table separately in a subquery. If you do the count in the main query, you're counting the results of the cross-product, which causes multiplication.

SELECT h.referer, hits, leads
FROM (SELECT referer, COUNT(*) AS hits
      FROM hits_log
      WHERE topic = 'topic0614'
      GROUP BY referer) AS h
JOIN (SELECT referer, COUNT(*) AS leads
      FROM leads_log
      GROUP BY referer) AS l
ON h.referer = l.referer

DEMO

Maybe this is actually what you want. It restricts both hits and leads to a specific topic, and will include referers with zero count in either table.

SELECT referer, MAX(hits) AS hits, MAX(leads) AS leads
FROM (SELECT referer, COUNT(*) AS hits, 0 as leads
      FROM hits_log
      WHERE topic = 'topic0614'
      GROUP BY referer
      UNION
      SELECT referer, 0 AS hits, COUNT(*) as leads
      FROM leads_log
      WHERE topic = 'topic0614'
      GROUP BY referer) AS x
GROUP BY referer

DEMO

edited Jul 5, 2014 at 13:23

answered Jul 5, 2014 at 12:39

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

4 Comments

Lucifer Over a year ago

it is only giving one single row if two referer against a single topic it is not giving 2nd row

Barmar Over a year ago

I'm not sure what you mean. Take a look at my sqlfiddle demo. I'm not totally sure what you really want, because your sample output doesn't seem to match the sample input -- see my question in the comments.

Barmar Over a year ago

All the hits for topic0614 have referer xxxxxxxx in your fiddle, so there's no other h.referer.

Barmar Over a year ago

See the query I just added.

Collectives™ on Stack Overflow

sql multi count from different different table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related