Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The Count Puzzle

The challenge here to write the smallest query. We have to summarized data based on the building Id. Please check the sample input and expected output.

Sample Input

tbl_RealEstate

BldID RealEstateName
1 Building 1
2 Building 2
3 Building 3
4 Building 4
5 Building 5
6 Building 6
7 Building 7
8 Building 8
9 Building 9

tbl_login

ID BldID Name
1 1 Name1
2 1 Name2
3 2 Name3
4 3 Name4
5 2 Name5
6 4 Name6
7 5 Name7
8 6 Name8
9 1 Name9
10 1 Name10
11 2 Name11
12 3 Name12

tbl_history

ID BldID Username
1 1 Name1
2 1 Name1
3 1 Name9
4 2 Name3
5 3 Name4
6 4 Name6
7 4 Name6
8 4 Name6
9 5 Name7
10 6 Name8
11 8 Name9
12 8 Name10
13 9 Name11
14 9 Name12

Expected Output

BldID RealEstateName LoginCount HistoryCount
1 Building 1 4 3
2 Building 2 3 1
3 Building 3 2 1
4 Building 4 1 3
5 Building 5 1 1
6 Building 6 1 1
7 Building 7 0 0
8 Building 8 0 2
9 Building 9 0 2

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE tbl_login
(
	ID int,
	BldID int,
	Name varchar(30)
)
GO

insert into tbl_login
values
(1, 1, 'Name1'),
(2, 1, 'Name2'),
(3, 2, 'Name3'),
(4, 3, 'Name4'),
(5, 2, 'Name5'),
(6, 4, 'Name6'),
(7, 5, 'Name7'),
(8, 6, 'Name8'),
(9, 1, 'Name9'),
(10, 1, 'Name10'),
(11, 2, 'Name11'),
(12, 3, 'Name12');
GO

CREATE TABLE tbl_history
(
	ID int,
	BldID int,
	Username varchar(30)
)
GO


insert into tbl_history
values
(1, 1, 'Name1'),
(2, 1, 'Name1'),
(3, 1, 'Name9'),
(4, 2, 'Name3'),
(5, 3, 'Name4'),
(6, 4, 'Name6'),
(7, 4, 'Name6'),
(8, 4, 'Name6'),
(9, 5, 'Name7'),
(10, 6, 'Name8'),
(11, 8, 'Name9'),
(12, 8, 'Name10'),
(13, 9, 'Name11'),
(14, 9, 'Name12');
GO

CREATE TABLE tbl_RealEstate
(
	 BldID int,
	RealEstateName varchar(30)
)
GO

insert into tbl_RealEstate values
(1, 'Building 1'),
(2, 'Building 2'),
(3, 'Building 3'),
(4, 'Building 4'),
(5, 'Building 5'),
(6, 'Building 6'),
(7, 'Building 7'),
(8, 'Building 8'),
(9, 'Building 9');



--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1


--

SELECT * FROM tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2

--

Output

--

/*------------------------
SELECT * FROM tbl_RealEstate as r 
OUTER APPLY
(
	SELECT COUNT(*) as LoginCount FROM tbl_login as l 
	WHERE BldID = r.BldID
)t1
OUTER APPLY
(
	SELECT COUNT(*) as HistoryCount FROM tbl_history as h
	WHERE BldID = r.BldID
)t2
------------------------*/
BldID       RealEstateName                 LoginCount  HistoryCount
----------- ------------------------------ ----------- ------------
1           Building 1                     4           3
2           Building 2                     3           1
3           Building 3                     2           1
4           Building 4                     1           3
5           Building 5                     1           1
6           Building 6                     1           1
7           Building 7                     0           0
8           Building 8                     0           2
9           Building 9                     0           2

(9 row(s) affected)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements