MYSQL Left Join COUNTS from multiple tables

Question

I want to add columns that represent counts from other tables.

I have 3 tables.

Messages

MessageID    User      Message      Topic
1            Tom       Hi           ball
2            John      Hey          book
3            Mike      Sup          book
4            Mike      Ok           book

Topics

Topic      Title     Category1    Category2
ball       Sports    Action       Hot
book       School    Study        Hot

Stars_Given

starID     Topic
1          ball
2          book
3          book
4          book

I want to end up with:

Topic_Review

Topic    Title     StarCount    UserCount    MessageCount
ball     Sports    1            1            1
book     school    3            2            3

So basically I want to attach 3 columns with counts of unique values (number of stars given within each topic, unique users who have messages within topic, and the number of unique messages in each topic).

I want to eventually be able to filter on the categories (look in both columns) as well.

Also, I want to eventually sort by the counts that I join. Example, I'm going to have a button that sorts by "number of stars" by ascending order, or sort by "number of users" by descending order, etc.

I've tried adapting other people's answers and I can't get it to work properly.

Michael Fredrickson · Accepted Answer · 2013-03-11T21:58:08.613

120

select
  t.Topic,
  t.Title,
  count(distinct s.starID) as StarCount,
  count(distinct m.User) as UserCount,
  count(distinct m.messageID) as MessageCount
from
  Topics t
  left join Messages m ON m.Topic = t.Topic
  left join Stars_Given s ON s.Topic = t.Topic
group by
  t.Topic,
  t.Title

Sql Fiddle

Or, you can perform the aggregation in sub-queries, which will likely be more efficient if you have a substantial amount of data in the tables:

select
  t.Topic,
  t.Title,
  s.StarCount,
  m.UserCount,
  m.MessageCount
from
  Topics t
  left join (
    select 
      Topic, 
      count(distinct User) as UserCount,
      count(*) as MessageCount
    from Messages
    group by Topic
  ) m ON m.Topic = t.Topic
  left join (
    select
      Topic, 
      count(*) as StarCount
    from Stars_Given 
    group by Topic
  ) s ON s.Topic = t.Topic

Sql Fiddle

edited Mar 11 '13 at 21:58

answered Mar 11 '13 at 21:42

Michael Fredrickson

36,839
5
92
109

`s.starID` appears to be unique, so you shouldn't need a distinct on that column (but it's not 100% from the question) – Matt Busche Mar 11 '13 at 21:48
6

@MattBusche The `distinct` is needed in the first query because the joins to the multiple tables will throw off the counts without it (3 becomes 9). – Michael Fredrickson Mar 11 '13 at 22:01
@tomuky What error are you getting? If I paste the code in your question into Sql Fiddle, the only issue I have is with the `description` column not existing, otherwise it works for me... – Michael Fredrickson Mar 11 '13 at 22:44
In PHP, I'm getting "Warning: mysql_fetch_assoc() expects parameter 1 to be resource" when I do a mysql_fetch_assoc on the mysql_query. (The description column is there. I omitted it to keep the example simple.) – Tom Mar 11 '13 at 22:45
@tomuky I don't think that is a problem with your query... I think that's problem in your PHP, which unfortunately I'm not qualified to help with... – Michael Fredrickson Mar 11 '13 at 22:47
@MichaelFredrickson is there a reason why a particular table wouldn't work? Because your 2nd solution works when I omit the stars table. And I've deleted the table and recreated but it still doesn't work. Are there some things to check with the table's actual structure for it to be used in a LEFT JOIN? Thanks! – Tom Mar 12 '13 at 21:25
@tomuky I don't think so... if you're getting the same PHP error, I don't think it's an issue with your table definition or your query. You can validate this by running the query directly against MySql, or by using the Sql Fiddle included in the answer. My suggestion would be to create a new question that includes the PHP code you're using to retrieve the query results, and the error that you're receiving. – Michael Fredrickson Mar 12 '13 at 21:28
@MichaelFredrickson I ran the query directly against MySql and got this error: "#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='." So I changed the 'stars' table to match the collation of the others and now it works! Thanks for all the help! – Tom Mar 12 '13 at 21:56
5

I was not aware you could use DISTINCT inside COUNT. Thank You. – Ryan Williams Sep 06 '13 at 00:19
@MichaelFredrickson why does `3 become 9` when `s.starID` is unique? – Tom Nov 13 '15 at 12:55
2

@Tom `count(s.starID)` won't count the number of unique values for `s.starID`... instead, this gives you the number of non-null values for `starID` before the grouping is applied. [If you were to remove the grouping from the first SQL fiddle query](http://sqlfiddle.com/#!2/3cf96/132), you'll see that there are 9 rows for `Topic = 'book'` and `Title = 'School'`... so unless `distinct` is added to the `count`, you'll instead be counting the number of rows with non-null values rather than the number of unique values. – Michael Fredrickson Nov 13 '15 at 17:22
@MichaelFredrickson okay -- is it possible to do a count *after* the grouping is applied? – Tom Nov 14 '15 at 15:42
@Tom You could group in a sub-query, and count in an outer query... but it sounds like you might have a specific problem that you're looking to solve. You might consider asking a new question on that specific problem to get a more appropriate answer. – Michael Fredrickson Nov 16 '15 at 16:30
superb @MichaelFredrickson i used your concept thanks – Pankaj katiyar Feb 09 '16 at 13:42
4

The use of DISTINCT in COUNT make difference in my queries with multiple LEFT JOINs. Thanks @MichaelFredrickson – Marcelo Gomes Jun 22 '16 at 08:25
The `DISTINCT` is needed because multiple tables are being joined at once which produces duplicate rows (`s*m` rows for each `t`). Is it not more efficient to do the joins in separate queries producing no duplicate rows (ie `s+m` rows)? – david_adler Jan 12 '17 at 17:01
Thank you, man! `distinct` really works! I used it to count rows of `LEFT JOIN` like: `SELECT COUNT(DISTINCT joined_table.id)` – Yamashiro Rion May 27 '19 at 12:39

MYSQL Left Join COUNTS from multiple tables

1 Answers1

Sql Fiddle

Sql Fiddle

Linked

Related