1

I have two tables,

  1. Labels -> id,name,description, user, status
  2. Label_connection. -> id, Label_id, categories

So there are multiple categories, let's say 1 => new, 2 => old.

I need the counts of the child table according to the category.

This is what I have right now,

SELECT `L`.*, COUNT(DISTINCT LC1.id) as count1, COUNT(DISTINCT LC2.id) as count2 
FROM (`Labels` L) 
LEFT JOIN `Label_connection` LC1 ON `LC1`.`Label_id` = `L`.`id` AND LC1.categories = "1" 
LEFT JOIN `Label_connection` LC2 ON `LC2`.`Label_id` = `L`.`id` AND LC2.categories = "2" 
WHERE `L`.`status` = '0' AND `L`.`user` = 1
GROUP BY `L`.`id` 
ORDER BY `L`.`id` DESC 
LIMIT 20

This does get me the counts correctly, But I am concerned about the multiple joins, as the number of categories will go up.

If anybody can improve this I would be grateful,

Thanks in advance.

bibin baby
  • 72
  • 9

2 Answers2

1

You could try writing it as one JOIN with conditional aggregation:

SELECT `L`.*
     , SUM(CASE WHEN LC.categories = "1" THEN 1 END) as count1
     , SUM(CASE WHEN LC.categories = "2" THEN 1 END) as count2
FROM `Labels` L
LEFT JOIN `Label_connection` LC ON `LC`.`Label_id` = `L`.`id`
WHERE `L`.`status` = '0' AND `L`.`user` = 1
GROUP BY `L`.`id` 
ORDER BY `L`.`id` DESC 
LIMIT 20
Nick
  • 138,499
  • 22
  • 57
  • 95
  • thanks for the answer, the answer below from Madhur is using COUNT instead of Sum, is there a big difference between them. I prefer COUNT over SUM. Thanks again. – bibin baby Dec 01 '18 at 09:47
  • @bibinbaby I don't think there would be a significant difference between the two, although having to use `DISTINCT` might slow the `COUNT` down. – Nick Dec 01 '18 at 11:23
1

For conditional aggregation, you can also use COUNT DISTINCT .. CASE..WHEN..END.

Also, your current query is not valid GROUP BY (unless L.id is a primary key). For details, read: Error related to only_full_group_by when executing a query in MySql

Also, please prefer to use SQL-Standard single quotes around String literals instead of double quotes.

SELECT L.id,  
       COUNT(DISTINCT CASE WHEN LC.categories = '1' THEN LC.id END) AS count1, 
       COUNT(DISTINCT CASE WHEN LC.categories = '2' THEN LC.id END) AS count2 
FROM Labels AS L
LEFT JOIN Label_connection AS LC 
  ON LC.Label_id = L.id
WHERE L.status = '0' AND
      L.user = 1
GROUP BY L.id 
ORDER BY L.id DESC 
LIMIT 20
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • That was perfect, Thanks again, is there a difference between SUM and COUNT, in performance. – bibin baby Dec 01 '18 at 09:51
  • 1
    @bibinbaby `COUNT()` **may** be able to use indexing in this case; however you can run `EXPLAIN` statements for both the queries and compare. Since you are counting rows here, count() seems more intuitive though. – Madhur Bhaiya Dec 01 '18 at 10:01
  • Thanks for the quick reply. I am going with COUNT. Plus 'L.id' is a primary key, added the group by so that nothing repeats and no null values get returned. – bibin baby Dec 01 '18 at 10:08