How to select columns and count(*)? in SQL

Question

I have tables like this:

User (idUser, nameUser, etc)

Chat (idChat, txtChat)

Members(idUser, idChat)

I have to select all the columns in User and in how many group chats (with more than 2 members) is said user and in how many regular chats (two members).

My first idea was to make two or more selects and use a union but it tourns out it doesn't quite work like that.

I tried something like this

    select *
    from User
    where idUser in (select idUser
                     from Members)

I tried getting the users that were in chats but I really don't know how to count aswell

or something like that, I don't really know where to put count (*) I know how to count the number of rows a select gets me but I don't know how to get it as another column

Answer 1

Something like this should do things for you (with GROUP BY):

SELECT  u.idUser,
        u.nameUser,
        COUNT(DISTINCT m.idChat) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser
GROUP BY u.idUser, u.nameUser

Or with PARTITION BY

SELECT DISTINCT 
        u.idUser,
        u.nameUser,
        COUNT(m.idChat) OVER (PARTITION BY nameUser) as countChats
FROM [User] u
LEFT JOIN Members m
    ON u.idUser = m.idUser