T-SQL SELECT with GROUP BY identifier

Question

T-SQL SELECT with GROUP BY identifier

I have a table with People '

Columns

ID
Name
Age

And the table with Notes ':

Columns

ID
Text
FK_Author

I want to select the number of notes for all authors from the people table, their name and age, but I want to group it by PERSON identifier, not by name. There are many situations where people have the same name, but the identifier 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 also need to group by name if I want to select this column, because if I do not want, I get an error.

+5

sql select group-by

Ellbar Apr 3 '13 at 11:18

source share

4 answers

SELECT P.ID,P.Name,P.Age,COUNT(N.ID)
FROM People P 
INNER JOIN Notes N ON N.FK_Author = P.ID
GROUP BY P.ID,P.Name,P.age

+3

Werner Waage 03 . '13 11:22

you can also use a subquery to complete this task as -

select people.Name,people.Age,(select count(notes.id) 
                                 from notes 
                                where notes.FK_Author= people.id)
from people;

+2

pratik garg Apr 3 '13 at 11:26

source share

You can also first get an invoice from the Notes table, and then join the People table, as shown below.

Fiddle-demo (thanks to JW for violin)

select name, age, Notate_Count
from People p left join (
        select fk_author, count(*) Notate_Count
        from Notes 
        group by fk_author ) x
     on p.Id = x.fk_author
order by name --Ordering by name here, change as required

|  NAME | AGE | NOTATE_COUNT |
------------------------------
| Annie |  29 |            2 |
|  John |  44 |            1 |
|  John |  12 |            2 |

+2

Kaf Apr 3 '13 at 11:27

source share

John Woo · Accepted Answer · 2013-04-03T11:20:58+0000

People, Notes LEFT JOIN, Notes v totalCount .

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

SQLFiddle Demo

, :

SQL

╔════╦═══════╦═════╦════════════╗
║ ID ║ NAME  ║ AGE ║ TOTALCOUNT ║
╠════╬═══════╬═════╬════════════╣
║  1 ║ John  ║  12 ║          2 ║
║  2 ║ Annie ║  29 ║          2 ║
║  3 ║ John  ║  44 ║          1 ║
╚════╩═══════╩═════╩════════════╝

T-SQL SELECT with GROUP BY identifier

More articles: