Using "GROUP BY" while joining two tables in SQL

Question

I've run into an issue I can't seem to figure out and haven't found any solution out on the web.

I have two tables; One with Authors and one with books. I need to summarize the information but I can't get the group by condition to work or count for that matter, but I think that's caused by my problems with group by.

My code is:

SELECT
    a.ID,
    a.FirstName + ' ' + a.LastName AS [Name],
    CASE WHEN a.DeathDate IS NULL
        THEN DATEDIFF(YEAR,a.BirthDate,SYSUTCDATETIME())
        ELSE DATEDIFF(YEAR,a.BirthDate,a.DeathDate)
    END AS [Age]
    ,COUNT(a.ID) AS [Books]
    
FROM Author a LEFT JOIN Books b ON a.ID = b.AuthorID 

group by a.ID

If I remove the count and group by I get a table where the names of the authors repeat, because some have written several book, which is to be expected. What I'm trying to do is count the number of books they have written, add that as a column and remove the name repetition.

Table without group by & count:

|      ROW    |     ID     |     Name   |     Age    |
|-------------|------------|------------|------------|
|      1      |     1      |    Adam    |     50     |
|      2      |     1      |    Adam    |     50     |
|      3      |     2      |    Sven    |     36     |
|      4      |     2      |    Sven    |     36     |
|      5      |     3      |    Eric    |     82     |
|      6      |     4      |    Rick    |     75     |

Table I wish i had:

|      ROW    |     ID     |     Name   |     Age    |     Books  |
|-------------|------------|------------|------------|------------|
|      1      |     1      |    Adam    |     50     |     2      |
|      2      |     2      |    Sven    |     36     |     2      |
|      3      |     3      |    Eric    |     82     |     1      |
|      4      |     4      |    Rick    |     75     |     1      |

Can anyone help?

You need to `GROUP BY` *all* the columns you aren't aggregating. You only have `a.ID` in your `GROUP BY`, but you have 4 other columns you aren't aggregating. — Thom A, Apr 08 '22 at 09:02
Skip the JOIN and GROUP BY. Have a correlated subquery to count each author's number of books. — jarlh, Apr 08 '22 at 09:03
Duplicate of [Select a Column in SQL not in Group By](https://stackoverflow.com/q/11991079/3404097) — philipxy, Apr 08 '22 at 09:13
@Larnu, I think I might be stupid or my teacher is not very good, not sure which. I for some reason thought that ```GROUP BY``` just grouped up the lines if they had the same value in the mentioned column. — Adam Degerman, Apr 08 '22 at 10:31

score 1 · Accepted Answer · answered Apr 08 '22 at 09:09

1

Correlated subquery sounds good:

SELECT
    a.ID,
    a.FirstName + ' ' + a.LastName AS [Name],
    CASE WHEN a.DeathDate IS NULL
        THEN DATEDIFF(YEAR,a.BirthDate,SYSUTCDATETIME())
        ELSE DATEDIFF(YEAR,a.BirthDate,a.DeathDate)
    END AS [Age]
    ,q1.books_count AS [Books]
    
FROM 
    Author a 
    outer apply(
        select count(*) as books_count
        from Books b 
        where a.ID = b.AuthorID 
    ) as q1

answered Apr 08 '22 at 09:09

George Menoutis

6,894
3
19
43

Cool thanks man, never seen "outer apply" before, guess I'll read up on that. – Adam Degerman Apr 08 '22 at 10:23

Using "GROUP BY" while joining two tables in SQL

1 Answers1