CODEWITHSUNDEEP
sqlsql-serverjoincount
Tanuki
Tanuki

Reputation: 363

Query count() on multiple columns

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

Upvotes: 1

Views: 99

Answers (2)

forpas
forpas

Reputation: 164099

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

Upvotes: 1

zealous
zealous

Reputation: 7503

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

Upvotes: 1

Related Questions