Counting specific records from 2 different Tables

Question

Post reply

Counting specific records from 2 different Tables

Shamshad Ali

SSChasing Mays

Points: 604
More actions
July 9, 2007 at 11:34 pm

#119676

I have a Master Table MyTable
MyTable
Auto_ID Name DOB
1 Shamshad 04-Feb-74
2 Zulfiqar 05-Jul-77
3 Talha 15-May-82
4 Waqar 13-May-75
5 Nadeem 15-Dec-80
I have two Child Tables:
Library Channel
Auto_ID Name Completed Auto_ID Name Completed
1 Shamshad 0 1 Shamshad 1
2 Shamshad 1 2 Waqar 1
3 Shamshad 1 3 Shamshad 1
4 Zulfiqar 0 4 Waqar 0
5 Zulfiqar 0 5 Talha 0
6 Shamshad 1
The relationship key is [Name], I need the Following result:
Result
Name DOB Tot. Library Visits Completed Tot. Channel Visits Completed
Shamshad 04-Feb-74 4 3 2 2
Zulfiqar 05-Jul-77 2 0 0 0
Talha 15-May-82 0 0 1 0
Waqar 13-May-75 0 0 2 1
Nadeem 15-Dec-80 0 0 0 0
I'm using following query:
select MyList.[Name],
Count(case when Library.Auto_ID>=1 then 1 else NULL end) [Total in Library],
Count(case Library.completed when 1 then Library.completed else NULL end) as LibraryCompleted
from MyList inner join Library on MyList.Name = Library.Name
group by MyList.[Name]

UNION

select MyList.[Name],
Count(case when Channel.Auto_ID>=1 then 1 else NULL end) [Total in Channel],
Count(case Channel.completed when 1 then Channel.completed else NULL end) as ChanneCompleted
from MyList inner join Channel on MyList.Name = Channel.Name
group by MyList.[Name]

This query is producing something different but again we need to put grouping on [Name] and keep columns separate to retrieve the output like I require above. I can also put date as parameter to see only those [Name] who's DOB year between 1974 AND 1980.

I want to run one single query to get output. Is it possible, how. Plz. help.

Shamshad Ali.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

Try a single select with outer joins to MyTable...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 2

There is the sql:

SELECT MyList.[Name], MyList.DOB,

Count(Library.[Name]) as [Total in Library],Sum(Library.Completed) as [LibraryCompleted],

Count(Channel.[Name]) as [Total in Channel],Sum(Channel.Completed) as [ChannelCompleted]

FROM MyList LEFT OUTER JOIN

Library ON MyList.[Name] = Library.[Name]

LEFT OUTER JOIN

Channel ON MyList.[Name] = Channel.[Name]

group by MyList.Name,MyList.DOB

Good luck!

In Theory, theory and practice are the same...In practice, they are not.

Shamshad Ali SSChasing Mays Points: 604 More actions · Answer 3

Sorry, it works logically, but it produces wrong resultset. I have tested it before my submission here. Better if someone faced this situation and solved it in a single query solving performance issue too, can reply.

I suggest you make these three tables in a test database and see results.

Anyway thanks for you all and help.

Shamshad Ali.

Shamshad Ali SSChasing Mays Points: 604 More actions · Answer 4

Here is my query result if I use left outer join:

select MyList.[Name], convert(varchar, DOB, 107) as DOB,

Count(case when Library.Auto_ID>=1 then 1 else NULL end) [Total in Library],

Count(case Library.completed when 1 then Library.completed else NULL end) as LibraryCompleted,

Count(case when Channel.Auto_ID>=1 then 1 else NULL end) [Total in Channel],

Count(case Channel.completed when 1 then Channel.completed else NULL end) as ChanneCompleted

from MyList left outer join Library on MyList.Name = Library.Name

left outer join Channel on MyList.Name = Channel.Name

--where Year(MyList.DOB) between '1974' and '1980'

group by MyList.[Name], MyList.DOB

OUTPUT: (which is wrong)

Name	DOB	Tot. In Library	Library com.	Tot. in Channel	channelComp
Nadeem	15-Dec-80	0	0	0	0
Shamshad	4-Feb-74	8	6	8	8
Talha	15-May-82	0	0	1	0
Waqar	13-May-75	0	0	2	1
Zulfiqar	5-Jul-77	2	0	0	0

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Shamshad, both Sorin and Jeff were absolutely correct. You need left joins. Your query didn't work because the aggregates in your query were incorrectly specified.

This will help you: test data and the result you expect. Please note that the query is essentially the same as Sorin's (apart from ISNULL() to change NULL to zero).

DROP TABLE #MyTable
CREATE TABLE #MyTable (Auto_ID int, [Name] VARCHAR(10), DOB DATETIME)

INSERT INTO #MyTable (Auto_ID, [Name], DOB)
SELECT 1, 'Shamshad', '04-Feb-74' UNION ALL 
SELECT 2, 'Zulfiqar', '05-Jul-77' UNION ALL  
SELECT 3, 'Talha', '15-May-82' UNION ALL  
SELECT 4, 'Waqar', '13-May-75' UNION ALL  
SELECT 5, 'Nadeem', '15-Dec-80' 
--SELECT * FROM #MyTable -- sanity check

DROP TABLE #Library
CREATE TABLE #Library (Auto_ID int, [Name] VARCHAR(10), Completed tinyint)

INSERT INTO #Library (Auto_ID, Name, Completed)
SELECT 1, 'Shamshad', 0 UNION ALL
SELECT 2, 'Shamshad', 1 UNION ALL  
SELECT 3, 'Shamshad', 1 UNION ALL  
SELECT 4, 'Zulfiqar', 0 UNION ALL  
SELECT 5, 'Zulfiqar', 0 UNION ALL  
SELECT 6, 'Shamshad', 1 
--SELECT * FROM #Library -- sanity check

DROP TABLE #Channel
CREATE TABLE #Channel (Auto_ID int, [Name] VARCHAR(10), Completed tinyint)

INSERT INTO #Channel (Auto_ID, Name, Completed)
SELECT 1, 'Shamshad', 1 UNION ALL 
SELECT 2, 'Waqar', 1 UNION ALL 
SELECT 3, 'Shamshad', 1 UNION ALL 
SELECT 4, 'Waqar', 0 UNION ALL 
SELECT 5, 'Talha', 0 
--SELECT * FROM #Channel -- sanity check

-- run query
SELECT m.[Name], m.DOB, 
 ISNULL(l.[Tot. Library Visits], 0), ISNULL(l.Completed, 0), 
 ISNULL(c.[Tot. Channel Visits], 0), ISNULL(c.Completed, 0)
FROM #MyTable m
LEFT JOIN (SELECT [Name], COUNT([Name]) AS [Tot. Library Visits], SUM(Completed) AS Completed
 FROM #Library 
 GROUP BY [Name]) l 
  ON l.[Name] = m.[Name]
LEFT JOIN (SELECT [Name], COUNT([Name]) AS [Tot. Channel Visits], SUM(Completed) AS Completed
 FROM #Channel 
 GROUP BY [Name]) c 
  ON c.[Name] = m.[Name]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 6

Hi Ali,

I have tested my query against a test database with exactly your data tables and it works well. As Chris said, I should replace NULL values with zeroes, but this is your choice.

Have a good day,

Sorin

In Theory, theory and practice are the same...In practice, they are not.

Shamshad Ali SSChasing Mays Points: 604 More actions · Answer 7

Thanks all for your great help, I have implemented and test it on my real data where I have been assigned to improve performance and the result should be same as it is working (developed by previous DBA). From above scenario, I have to exclude those if no one has visited library or any channel. like in above table "Nadeem" is the guy who has no entry in Library or Channel. how can i exclude it. I tried to use INNER Join in both library and Channel that is giving me only records that has entries in both like "Shamshad" is the only record who has entries in both tables.

SELECT m.[Name], m.DOB,

ISNULL(l.[Tot. Library Visits], 0),

ISNULL(l.Completed, 0),

ISNULL(c.[Tot. Channel Visits], 0),

ISNULL(c.Completed, 0)

FROM #MyTable m

inner JOIN

(

SELECT [Name], COUNT([Name]) AS [Tot. Library Visits],

SUM(Completed) AS Completed FROM #Library GROUP BY [Name]

) l ON l.[Name] = m.[Name]

inner JOIN

(SELECT [Name], COUNT([Name]) AS [Tot. Channel Visits],

SUM(Completed) AS Completed FROM #Channel GROUP BY [Name]) c ON c.[Name] = m.[Name]

OUTPUT:

Name	DOB	Tot.Lib. Visit	Lib comp.	Tot. Channel visit	channel comp.
Shamshad	4-Feb-74	4	3	2	2

Required:

Name	DOB	Tot.Lib. Visit	Lib comp.	Tot. Channel visit	channel comp.	Required or NOT
Shamshad	4-Feb-74	4	3	2	2	required
Zulfiqar	7/5/1977	2	0	0	0	required
Talha	5/15/1982	0	0	1	0	required
Waqar	5/13/1975	0	0	2	1	required
Nadeem	12/15/1980	0	0	0	0	No need. X

Shamshad Ali

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

-- run query
SELECT m.[Name], m.DOB,  
ISNULL(l.[Tot. Library Visits], 0), 
ISNULL(l.Completed, 0),  
ISNULL(c.[Tot. Channel Visits], 0), 
ISNULL(c.Completed, 0)
FROM #MyTable m
LEFT JOIN (SELECT [Name], COUNT([Name]) AS [Tot. Library Visits], SUM(Completed) AS Completed 
 FROM #Library  
 GROUP BY [Name]) l   ON l.[Name] = m.[Name]
LEFT JOIN (SELECT [Name], COUNT([Name]) AS [Tot. Channel Visits], SUM(Completed) AS Completed 
 FROM #Channel  
 GROUP BY [Name]) c   ON c.[Name] = m.[Name]
WHERE (l.[Name] IS NOT NULL OR c.[Name] IS NOT NULL)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Shamshad Ali SSChasing Mays Points: 604 More actions · Answer 9

Thanks for the help. I have implemented and it works perfect. By keeping same scenario furthermore, I require to input DOB @FromDate and @ToDate and @FirstName and @LastName as Parameter passed to this query, we have only [Name] field in MyTable. Which is combination of FirstName and LastName. we are using these parameters from Front-end. If these parameters are supplied otherwise it won't use any WHERE in TOP of the query, this is only when user input parameters otherwise it assumes there is no paramter supplied like following code:

Declare

@ToDate datetime,

@FromDate datetime,

@LastName varchar(100) ,

@FirstName varchar(100)

SELECT m.[Name], m.DOB,

ISNULL(l.[Tot. Library Visits], 0),

ISNULL(l.Completed, 0),

ISNULL(c.[Tot. Channel Visits], 0),

ISNULL(c.Completed, 0)

FROM #MyTable m

left JOIN

(

SELECT [Name], COUNT([Name]) AS [Tot. Library Visits],

SUM(Completed) AS Completed FROM #Library GROUP BY [Name]

) l ON l.[Name] = m.[Name]

left JOIN

(SELECT [Name], COUNT([Name]) AS [Tot. Channel Visits],

SUM(Completed) AS Completed FROM #Channel GROUP BY [Name]) c ON c.[Name] = m.[Name]

WHERE (l.[Name] IS NOT NULL OR c.[Name] IS NOT NULL)

AND m.[Name] LIKE @FirstName + '%_' AND m.[Name] LIKE '_%'+ @LastName

Here it won't work untill I set parameters with check condition like

IF @FirstName is null

set @FirstName = ''

IF @LastName is null

set @LastName = ''

If @FirstName is Null then I have use set it Empty like ''. Is there any good solution that may produce beter peformance and less code ? so that I can reduce if conditions

Shamshad Ali.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

This will work...

.

WHERE (l.[Name] IS NOT NULL OR c.[Name] IS NOT NULL)

AND m.[Name] LIKE

CASE WHEN @FirstName IS NULL

THEN m.[Name]

ELSE @FirstName + '%_'

END

.

...but it's inelegant and probably inefficient. I would suggest you set up the original query as a derived table then apply the new filters to a SELECT into that derived table.

you might also get some mileage out of concatenating @FirstName and @lastname:

SET @fullname = RTRIM(@FirstName) + '_' + RTRIM(@LastName)

Try it out, repost if you get stuck.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

MyTable
Auto_ID	Name	DOB
1	Shamshad	04-Feb-74
2	Zulfiqar	05-Jul-77
3	Talha	15-May-82
4	Waqar	13-May-75
5	Nadeem	15-Dec-80

Library			Channel
Auto_ID	Name	Completed	Auto_ID	Name	Completed
1	Shamshad	0	1	Shamshad	1
2	Shamshad	1	2	Waqar	1
3	Shamshad	1	3	Shamshad	1
4	Zulfiqar	0	4	Waqar	0
5	Zulfiqar	0	5	Talha	0
6	Shamshad	1

Result
Name	DOB	Tot. Library Visits	Completed	Tot. Channel Visits	Completed
Shamshad	04-Feb-74	4	3	2	2
Zulfiqar	05-Jul-77	2	0	0	0
Talha	15-May-82	0	0	1	0
Waqar	13-May-75	0	0	2	1
Nadeem	15-Dec-80	0	0	0	0