Using "GROUP BY" while joining two tables in SQL-CodePudding

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?

CodePudding user response：

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