Menu
suppose i have two tables. articles and comments.
when i am selecting columns from articles table, i also want to select the number of comments on the article in the same select statement... (suppose the common field between these two tables is articleid)
how do I do that? I can get it done, but I do not know if my way would be efficient, so i want to learn the right way.
848
In this short tutorial, you have seen how the COUNT/GROUP BY/JOIN combination can be used in SQL to aggregate entries across multiple tables. While a GROUP BY query can accomplish this simply enough when working with just one table, the situation can become more complex when working across multiple tables.
Left Outer Join returns all of the rows in the current data and all the data from the matching rows in the joined data, adding rows when there is more than one match. This can result in an expanded row count.
There are two line items for ID 1003 in the second table, so the result of the join will be 2 line items. So, if your secondary tables have more than one row for the key you're joining with, then the result of the join will be multiple rows, resulting in more rows than the left table.
COUNT(*) or COUNT(1) The seemingly obvious way to get the count of rows from the table is to use the COUNT function. There are two common ways to do this – COUNT(*) and COUNT(1).
Use:
SELECT a.articleid,
COUNT(*) AS num_comments
FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
GROUP BY a.articleid
Whatever columns you want from the ARTICLES table, you'll have to define in the GROUP BY clause because they aren't having an aggregate function performed on them.
62
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
