1

For exercise, I have created the following database.

I would like to be able to count() the number of dances in the table "dance" for each user.

This is the query

SELECT 
    [user].[id_user], [user].[user_name], 
    COUNT([dance].[dancer_2_id_user]) AS 'number of dances'
FROM 
    [user]
LEFT JOIN 
    [dance] ON [user].[id_user] = [dance].[dancer_2_id_user]
GROUP BY 
    [user].[id_user], [user].[user_name]

which returns the number of times the user is counted in the "dancer_2_id_user" column.

And this query

SELECT 
    [user].[id_user], [user].[user_name], 
    COUNT([dance].[dancer_1_id_user]) AS 'number of dances'
FROM 
    [user]
LEFT JOIN 
    [dance] ON [user].[id_user] = [dance].[dancer_1_id_user]
GROUP BY 
    [user].[id_user], [user].[user_name]

returns the number of times the user is counted in the "dancer_1_id_user" column.

I would like to put these two queries together as a user can appear in either column of the "dance" table to be able to count the total number of dances per user.

database schema

CREATE TABLE [user]
(
    [id_user] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [user_name] VARCHAR(45) UNIQUE,
    [User_Sex] CHAR(1),
    [date_of_birth] DATE,
    [account_type] INT,
    [id_address] INT,
);

CREATE TABLE [address]
(
    [id_address] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [street] VARCHAR(255),
    [number] INT,
    [locality] VARCHAR(255),
    [city] VARCHAR(255),
    [country_code] CHAR(2)
);

CREATE TABLE [membership]
(
    [account_type] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [membership_name] VARCHAR(45),
    [membership_price] DECIMAL(4,2)
);

CREATE TABLE [style]
(
    [style_ref] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
    [style_name] VARCHAR(45)
);

CREATE TABLE [dance]
(
    [id_dance] INT NOT NULL IDENTITY(1,1),
    [dancer_1_id_user] INT,
    [dancer_2_id_user] INT,
    [dance_dtg] DATETIME,
    [style_ref] INT,
    FOREIGN KEY (dancer_1_id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (dancer_2_id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)  
);

CREATE TABLE [user_dance_style]
(
    [id_user] INT,
    [style_ref] INT
    FOREIGN KEY (id_user) REFERENCES [user] (id_user),
    FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)
)

ALTER TABLE [user]
   ADD CONSTRAINT fk_user_memebership 
       FOREIGN KEY (account_type) REFERENCES membership (account_type),
       CONSTRAINT fk_user_address 
       FOREIGN KEY (id_address) REFERENCES address (id_address);

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

INSERT INTO [membership] ([membership_name], [membership_price])
VALUES ('free', '0'), ('regular', '15'), ('premium', '30')

INSERT INTO [style]([style_name])
VALUES ('Salsa'), ('Bachata'), ('Kizomba')
GO

INSERT INTO [user] ([user_name], [User_Sex], [date_of_birth], [account_type], [id_address])
VALUES ('sara', 'f', '1990-04-23', '1', '1'),
       ('elenor', 'f', '1989-02-18', '1', '2'),
       ('eva', 'f', '1987-01-04','1','3'),
       ('mike', 'm', '1985-05-02', '1', '4'),
       ('phil', 'm', '1985-03-01', '1', '5'),
       ('laurent', 'm', '1986-02-14', '2', '6'),
       ('nidia', 'f', '1985-01-16', '2', '7'),
       ('franz', 'm', '1990-03-17', '2', '8'),
       ('stephan', 'm', '1991-05-23', '2', '9'),
       ('sandra', 'f', '1993-03-25', '3', '10'),
       ('virginie', 'f', '1999-05-03', '3', '11'),
       ('claire', 'f', '1992-02-24', '3', '12'),
       ('laurence', 'f', '1991-04-26', '3', '13'),
       ('pierre', 'm', '1987-02-14', '3', '14'),
       ('thierry', 'm', '1989-01-04', '3', '15'),
       ('nancy', 'f', '1950-04-15', '1', '16')
GO

INSERT INTO [address] ([street], [number], [locality], [city], [country_code])
VALUES
('av de l''exposition', '13', 'laeken', 'bruxelles', 'be'),
('rue cans', '2', 'ixelles', 'bruxelles', 'be'),
('rue goffart', '32', 'ixelles', 'bruxelles', 'be'),
('ch de haecht', '17', 'schaerbeek', 'bruxelles', 'be'),
('rue metsys', '108', 'schaerbeek', 'bruxelles', 'be'),
('rue du pré', '223', 'jette', 'bruxelles', 'be'),
('rue sergent sorenser', '65', 'ganshoren', 'bruxelles', 'be'),
('rue d''aumale', '38', 'anderlecht', 'bruxelles', 'be'),
('av de fré', '363', 'uccle', 'bruxelles', 'be'),
('rue de lisbonne', '52', 'saint gilles', 'bruxelles', 'be'),
('av neptune', '24', 'forest', 'bruxelles', 'be'),
('av mozart', '76', 'forest', 'bruxelles', 'be'),
('rue emile delva', '92', 'laeken', 'bruxelles', 'be'),
('av de la chasse', '68', 'etterbeek', 'bruxelles', 'be'),
('rue leopold 1', '42', 'laeken', 'bruxelles', 'be'),
('av charle woeste', '68', 'jette', 'bruxelles', 'be')

GO

INSERT INTO [user_dance_style] ([id_user], [style_ref])
VALUES
(1, 1),(1, 2),(1, 3),(2, 1),(2, 2),(2, 3),(3, 1),(3, 2),(4, 1),(4, 2),
(4, 3),(5, 2),(5, 3),(6, 1),(7, 3),(8, 3),(9, 1),(9, 2),(9, 3),(10, 1),
(10, 2),(10, 3),(11, 3),(12, 2),(13, 2),(14, 1)

GO

INSERT INTO [dance]([dancer_1_id_user], [dancer_2_id_user], [dance_dtg], [style_ref])
VALUES
(1, 2, convert(datetime, '2019-11-24 10:34:09 PM',20), 3),
(4, 2, convert(datetime, '2019-11-24 10:50:00 PM',20), 3),
(3, 5, convert(datetime, '2019-11-24 10:35:00 PM',20), 2),
(6, 1, convert(datetime, '2019-11-24 10:37:00 PM',20), 1),
(7, 2, convert(datetime, '2019-11-24 10:37:00 PM',20), 3),
(8, 1, convert(datetime, '2019-12-03 11:20:03 PM',20), 3),
(9, 3, convert(datetime, '2019-12-23 10:45:00 AM',20), 1),
(10, 12, convert(datetime, '2019-12-26 11:20:00 AM',20), 2),
(11, 4, convert(datetime, '2020-01-02 08:45:00 AM',20), 3),
(12, 5, convert(datetime, '2020-01-02 11:10:04 AM',20), 2),
(13, 12, convert(datetime, '2020-02-04 09:25:00 PM',20), 2),
(14, 10, convert(datetime, '2020-02-25 10:45:00 AM',20), 1),
(2, 14, convert(datetime, '2020-02-25 08:45:00 PM',20), 1),
(5, 10, convert(datetime, '2020-03-01 11:15:06 AM',20), 2)
GO
Improve this question

2 Answers 2

Reset to default
1

You can join user to dance with both conditions using the operator IN:

SELECT u.[id_user], u.[user_name], 
       COUNT(d.id_dance) AS [number of dances]
FROM [user] u LEFT JOIN [dance] d
ON u.[id_user] IN (d.[dancer_1_id_user], d.[dancer_2_id_user])
GROUP BY u.[id_user], u.[user_name]

See the demo.
Results:

> id_user | user_name | number of dances
> ------: | :-------- | ---------------:
>       1 | sara      |                3
>       2 | elenor    |                4
>       3 | eva       |                2
>       4 | mike      |                2
>       5 | phil      |                3
>       6 | laurent   |                1
>       7 | nidia     |                1
>       8 | franz     |                1
>       9 | stephan   |                1
>      10 | sandra    |                3
>      11 | virginie  |                1
>      12 | claire    |                3
>      13 | laurence  |                1
>      14 | pierre    |                2
>      15 | thierry   |                0
>      16 | nancy     |                0
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I had no idea we can do something like this COUNT(COALESCE(d.[dancer_1_id_user], d.[dancer_2_id_user])). nice solution and I learnt something new today.
I already edited. You have a primary key in dance and you can use it instead of coalesce() which is better.
@forpas I wasn't aware of the usage of IN after the join, I only know the usage of IN with WHERE. I'm looking to find explanations on how to use it this way but I can't find other examples or tutorials. Could you point me to somewhere where it is explained?
There is nothing special about the use of IN in the ON clause. The operator IN returns a boolean value: TRUE or FALSE and it can be used wherever a boolean value is valid. Just like a.id = b.id returns TRUE or FALSE. You can read more here: learn.microsoft.com/en-us/sql/t-sql/language-elements/…
Thank you @forpas, actually, the examples in the link also show the usage of IN with the where clause but I think I start to get how to use it. I just reformated one of my queries using this magic trick.
1

Try the following using union all

select
    user_id,
    user_name,
    sum(number_of_dances) as number_of_dances
from
(
    SELECT 
        [user].[id_user] as user_id, 
        [user].[user_name] as user_name, 
        count([dance].[dancer_2_id_user]) as number_of_dances
    FROM [user]
    left JOIN [dance]
    ON [user].[id_user] = [dance].[dancer_2_id_user]
    GROUP BY [user].[id_user], [user].[user_name]

    union all


    SELECT 
        [user].[id_user], 
        [user].[user_name], 
        count([dance].[dancer_1_id_user])
    FROM [user]
    left JOIN [dance]
    ON [user].[id_user] = [dance].[dancer_1_id_user]
    GROUP BY [user].[id_user], [user].[user_name]

)val
group by
    user_id,
    user_name
Improve this answer

2 Comments

Thank you! I was literally just reading the chapter about "union" in my course :) what is the "val" following the from statement?
@Tanuki you have to provide alias to the subquery, it can be anything so I randomly chose val. So I first did union all of two queries and then sum of the dance count per user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.