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

Solution

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

Comments

[Lucas Araujo]

thanks a lot for the reply! I really don't understand what either distinct or partition are but this will help me get closer!

[gofr1]

DISTINCT - to get data without duplicates, PARTITION BY - to divide the query result set into partitions, it is similar to GROUP BY, but not the same.

[Lucas Araujo]

A problem has surfaced, I need to check if Chat is group or normal chat with a column which is either 0 or 1 and I don't know how to separete two chats, I tried doing something like select u.idUser, u.nameUser Count(Distinct m.idChat) as Chats from User u, Chat c1, Chat c2 but when it comes to "where" I don't know how to write it, I tried with c1.idChat=m.idChat or c2.idChat=m.idChat but I get weird results, any idea?

[gofr1]

What column in what table you determine if chat is group or normal? And NEVER use ',' instead of proper JOIN syntax. You should JOIN all this (from my answer) with Chat table by m.chatid = c.chatid and in WHERE add AND c.isGroup = 0

[Lucas Araujo]

It's a column in Chat, a bit either 0 or 1 0 for normal chat 1 for group chat

[Lucas Araujo]

yeah but with those that are not group chat only, I need for each User how many groups and normal chats is he in

[gofr1]

Then you need add c.isGroup or whatever this column is called to SELECT and GROUP BY parts of query. In SELECT you can use CASE WHEN c.isGriup =1 THEN 'Group' ELSE 'Normal' END as ChatType.