Menu
I have table with 'People'
Columns
And table with 'Notes':
Columns
I want to select count of notes for all authors from people table and their name and age, but I want to group it by PERSON ID, not name. There are many situations when people have same name, but ID is obviously always different.
EXAMPLE (input)
PEOPLE:
╔════╦═══════╦═════╗
║ ID ║ NAME ║ AGE ║
╠════╬═══════╬═════╣
║ 1 ║ John ║ 12 ║
║ 2 ║ Annie ║ 29 ║
║ 3 ║ John ║ 44 ║
╚════╩═══════╩═════╝
NOTES:
╔════╦═══════╦═══════════╗
║ ID ║ TEXT ║ FK_AUTHOR ║
╠════╬═══════╬═══════════╣
║ 1 ║ 'aaa' ║ 1 ║
║ 2 ║ 'aaa' ║ 1 ║
║ 3 ║ 'aaa' ║ 2 ║
║ 4 ║ 'aaa' ║ 2 ║
║ 5 ║ 'aaa' ║ 3 ║
╚════╩═══════╩═══════════╝
Expected result:
╔═══════╦═════╦════════════╗
║ NAME ║ AGE ║ TOTALCOUNT ║
╠═══════╬═════╬════════════╣
║ John ║ 12 ║ 2 ║
║ Annie ║ 29 ║ 2 ║
║ John ║ 44 ║ 1 ║
╚═══════╩═════╩════════════╝
When I select data I have to group by Name too if I want to select this column because if I dont, I get error.
556
You cannot write select inside the Group by clause.
GROUP BY Clause is utilized with the SELECT statement. GROUP BY aggregates the results on the basis of selected column: COUNT, MAX, MIN, SUM, AVG, etc. GROUP BY returns only one result per group of data. GROUP BY Clause always follows the WHERE Clause.
GROUPING_ID() is used to create a value for each row in the Title column that identifies its level of aggregation. SQL Copy.
When combining the Group By and Order By clauses, it is important to bear in mind that, in terms of placement within a SELECT statement: The GROUP BY clause is placed after the WHERE clause. The GROUP BY clause is placed before the ORDER BY clause.
Since you want to get all records from table People, you need to join it with Notes by using LEFT JOIN so any user without any record on Notes will be included in the list with thev value of totalCount with zero.
SELECT a.ID, a.Name, a.Age,
COUNT(b.FK_Author) totalCount
FROM People a
LEFT JOIN Notes b
ON a.ID = b.FK_Author
GROUP BY a.ID, a.Name, a.Age
To further gain more knowledge about joins, kindly visit the link below:
OUTPUT
╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║ 1 ║ John ║ 12 ║ 2 ║
║ 2 ║ Annie ║ 29 ║ 2 ║
║ 3 ║ John ║ 44 ║ 1 ║
╚════╩═══════╩═════╩════════════╝
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
