0

I have three tables, company, user and share. I want to count one company's user and share, they are not relevant.

There may be a row that has share value but not user value. so I used left join, I can get results separately, but it doesn't work together.

Here is my query:

SELECT c.name, count(u.company_id), count(s.company_id)
FROM company c
LEFT JOIN user u
ON c.id=u.company_id and u.company_id=337
WHERE u.company_id is NOT NULL 
LEFT JOIN share s
ON c.id=s.id AND s.company_id=337 
WHERE s.company_id is NOT NULL
Mike G
  • 4,232
  • 9
  • 40
  • 66
Meng Gao
  • 3
  • 4
  • Sintax is wrong. – Fran Cerezo May 25 '17 at 21:11
  • 1
    Apart from extra WHERE just before second LEFT join, are you sure that share.id is same as company.id? Maybe on the second LEFT JOIN it should be something like "ON c.id=s.company_id". – tsolakp May 25 '17 at 21:22
  • You can only have one `WHERE` clause, and it has to be after all the `JOIN` clauses. – Barmar May 25 '17 at 21:41
  • If you only want the rows where the company_id is not null, you should use `INNER JOIN` instead of `LEFT JOIN`. Then you don't need to check for `NULL`, because it only returns matching rows. – Barmar May 25 '17 at 21:42
  • 1
    And the two counts will be the same, because you're just counting the number of rows in the cross product, not the number of rows in the tables you're joining with. – Barmar May 25 '17 at 21:43
  • Edit your question and provide sample data and desired results. – Gordon Linoff May 26 '17 at 02:22

2 Answers2

1

You need to do at least one of the counts in a subquery. Otherwise, both counts will be the same, since you're just counting the rows in the resulting cross product.

SELECT c.name, user_count, share_count
FROM company AS c
JOIN (SELECT company_id, COUNT(*) AS user_count
      FROM users
      GROUP BY company_id) AS u
ON u.company_id = c.id
JOIN (SELECT company_id, COUNT(*) AS share_count
      FROM share 
      GROUP BY company_id) AS s
ON s.company_id = c.id
WHERE c.company_id = 337

Another option is to count the distinct primary keys of the tables you're joining with:

SELECT c.name, COUNT(DISTINCT u.id) AS user_count, COUNT(DISTINCT s.id) AS share_count
FROM company AS c
JOIN users AS u on u.company_id = c.id
JOIN share AS s ON s.company_id = c.id
WHERE c.company_id = 337
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • when I use join, for example, a join b on xxx join c on xxx, it is like a join b and a join c seperately? not a join b and the same part join c, right? – Meng Gao May 26 '17 at 15:00
  • if I want to count share for different time period, i did that, but results didn't match. – Meng Gao May 26 '17 at 15:29
  • SELECT c.name, COUNT(DISTINCT u.id) AS user_count, COUNT(DISTINCT s.id) AS share_count, count(distinct s1.id) FROM company AS c JOIN user AS u on u.company_id = c.id and u.created between '2017/02/20' and '2017/05/20' JOIN share AS s ON s.company_id = c.id and s. date between '2017/02/20' and '2017/05/20' JOIN share AS s1 ON s1.company_id = c.id and s. date between '2017/04/20' and '2017/05/20' WHERE c.id = 337 – Meng Gao May 26 '17 at 15:29
  • Whether the joins are seperate or combined depends on the conditions in the `ON` clause. If you do `JOIN c ON b.column = c.column` then it combines them. – Barmar May 26 '17 at 16:02
  • I think that query with the time periods should work. Can you make a sqlfiddle with the data? – Barmar May 26 '17 at 16:03
  • It works, but I have a new question now. Did not understand the answer they commented. Could you please have a look at that one? Thanks so much! here is the link:https://stackoverflow.com/questions/44208580/how-to-select-every-month-even-they-do-not-have-value/44210270#44210270 – Meng Gao May 29 '17 at 14:43
0

Your code looks okay, except for the extra WHERE clause. However, you probably want COUNT(DISTINCT), because the two counts will return the same value:

SELECT c.name, count(distinct u.company_id), count(distinct s.company_id)
FROM company c LEFT JOIN
     user u
     ON c.id = u.company_id and u.company_id=337 LEFT JOIN
     share s
     ON c.id = s.id AND s.company_id=337 
WHERE s.company_id is NOT NULL AND u.company_id IS NOT NULL;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    `COUNT(distinct u.company_id)` will always be `1` because you're just matching a single company ID. – Barmar May 25 '17 at 21:46