Agrregates from multi linked tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Agrregates from multi linked tables

Author

Topic

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-17 : 11:49:13

Hi

I received some wonderful help from visakh16 the other day on a similar post and I'm trying to follow the same format. However, this although similar has multiple joins and although it works fine I have a feeling it's not eh most effective way of aggregating from different tables

Here's what I have

SELECT
r.ReferralID
, COUNT (DISTINCT [PEPSID] ) AS [PEPSCOUNT]
, COUNT (DISTINCT CASE WHEN PEPSALT IS NOT NULL THEN [PEPSID] END) AS [PEPSALTCount]
, COUNT (DISTINCT CASE WHEN PEPSSUP IS NOT NULL AND PEPSSUPEnd IS NULL THEN [PEPSID] END) AS [PEPSSUPCount]
, COUNT (DISTINCT CASE WHEN TCB IS NOT NULL AND TC IS NULL THEN [TDID] END) AS [TDCCount]
, COUNT (DISTINCT CASE WHEN TCB IS NOT NULL AND TC IS NOT NULL THEN [TDID] END) AS [TCCount]
, COUNT (DISTINCT [TDID]) AS [TDCount]
, COUNT (DISTINCT [NID]) AS [NCount]

FROM TblReferral AS r
INNER JOIN [TbNms] AS n on r.ReferralID = n.NmsReferralID
INNER JOIN TblTD As t on r.ReferralID = t.TDferralID
INNER JOiN TblCN AS c on r.ReferralID = c.CnReferralID

GROUP BY r.ReferralID
order by r.ReferralID

I've only just migrated to sql server from access and still learning so any pointers re bad practice in the above would also be much appreciated

Thaks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 11:59:16

if the tables are involved in one to many or many to many relationships then my preferred way is to do aggregation inside derived tables and then join them. As per above query not clear from which tables each columns to be aggregated comes. If you can specify that I'll provide the code.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-17 : 12:14:52

Many Thanks

ONE SIDE AND PRIMARYKEY .......MANY SIDE AND RELATED KEY
TblReferral.ReferralID.......to......TbNms.NmsReferralID
TblReferral.ReferralID.......to......TblTD.TDferralID
TblReferral.ReferralID.......to......TblCN.CnReferralID

Hope this makes sense

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-17 : 12:27:32

Sorry ...No
I was asking on the columns you used inside COUNT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-17 : 12:43:24

Table...........Field
TblReferral... ReferralID
TbNms......... PEPSID
TbNms......... PEPSALT,PEPSID
TbNms......... PEPSSUP,PEPSSUPEnd,PEPSID
TblTD......... TCB,TC
TblTD......... TCB,TC,TDID
TblTD......... TCB,TC,TDID
TblCN......... NID

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-17 : 19:26:27

Reading back it's not very clear still so here goes again

All are joined to a common table TblReferral on ReferralID

COUNT (DISTINCT [PEPSID] ) AS [PEPSCOUNT]
Table.....TbNms
Field.......PEPSID (UniqueID)
Join field..NmsReferralID

COUNT (DISTINCT CASE WHEN PEPSALT IS NOT NULL THEN [PEPSID] END) AS [PEPSALTCount]
Table.....TbNms
fields......PEPSALT (DateTime)
..............PEPSID (UniqueID)
Join field...NmsReferralID

COUNT (DISTINCT CASE WHEN PEPSSUP IS NOT NULL AND PEPSSUPEnd IS NULL THEN [PEPSID] END) AS [PEPSSUPCount]
Table......TbNms
fields......PEPSSUP (DateTime)
.............PEPSSUPEnd (DateTime)
.............PEPSID (UniqueID)
Join field...NmsReferralID

COUNT (DISTINCT CASE WHEN TCB IS NOT NULL AND TC IS NULL THEN [TDID] END) AS [TDCCount]
Table.....TblTD
Field.......TCB (DateTime)
............TC (DateTime)
............TDID (UniqueID)
Join Field..TDferralID

COUNT (DISTINCT CASE WHEN TCB IS NOT NULL AND TC IS NOT NULL THEN [TDID] END) AS [TCCount]
Table.....TblTD
Field.......TCB (DateTime)
............TC (DateTime)
............TDID (UniqueID)
Join Field..TDferralID

COUNT (DISTINCT [TDID]) AS [TDCount]
Table.....TblTD
Field.......TDID (UniqueID)
Join Field..TDferralID

COUNT (DISTINCT [NID]) AS [NCount]
Table.....TblCN
Field.......NID (UniqueID)
Join Field..CnReferralID

AND One final one not mentioned before which is causing ne some problems(not sure if it's possible like this)
SUM ([NCost]) AS [Cost]
Table.......TblCN
Field.......NCost (Money)
Join Field..CnReferralID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 05:53:08

[code]
SELECT r.ReferralID,
[PEPSCOUNT],
[PEPSALTCount],
[PEPSSUPCount],
[TDCCount],
[TCCount],
[TDCount],
[NCount],
[Cost]
FROM TblReferral r
LEFT JOIN (
SELECT NmsReferralID,
COUNT ([PEPSID] ) AS [PEPSCOUNT],
COUNT (CASE WHEN PEPSALT IS NOT NULL THEN [PEPSID] END) AS [PEPSALTCount],
COUNT (CASE WHEN PEPSSUP IS NOT NULL AND PEPSSUPEnd IS NULL THEN [PEPSID] END) AS [PEPSSUPCount]
FROM TbNms
GROUP BY NmsReferralID
)n
ON n.NmsReferralID = r.ReferralID
LEFT JOIN (
SELECT TDferralID,
COUNT (CASE WHEN TCB IS NOT NULL AND TC IS NULL THEN [TDID] END) AS [TDCCount],
COUNT (CASE WHEN TCB IS NOT NULL AND TC IS NOT NULL THEN [TDID] END) AS [TCCount].
COUNT ([TDID]) AS [TDCount]
FROM TblTD
GROUP BY TDferralID
)td
ON td.TDferralID = r.ReferralID
LEFT JOIN (
SELECT CnReferralID,
COUNT ([NID]) AS [NCount],
SUM ([NCost]) AS [Cost]
FROM TblCN
GROUP BY CnReferralID
)cn
ON cn.CnReferralID = r.ReferralID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-18 : 05:54:49

Thank you again visakh16

The pointer to derived tables was the golden key, I've bee reading up on them all day and although I've only managed a single joined derived table so far I think it'll be really neat once I've found the syntax for multiple derived tables

Here's my first step (concept rather than the query itself) with BIG thanks to you for the direction

SELECT r.ReferralID,PEPSCOUNT
from TblReferral AS r
JOIN (
SELECT NmsReferralID
, COUNT([PEPSID]) AS PEPSCOUNT
from TbNms
Group by NmsReferralID
) TmpPIPS
ON r.ReferralID = TmpPIPS.NmsReferralID

Take care

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 06:01:42

You're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-18 : 06:11:02

Oh sorry I missed your kind and thoughtful post before mine. sorry

How many times can I thank you without sounding a little silly? You really are a nice guy.

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-18 : 06:38:03

Just in case anyone else follows this post, and going back to my original question. is there more effective way of aggregating from different tables?

Well, the The original code executed on 1700 rows in TblReferral and linked to 4000 - 30,000 (ish) rows in related tables took around 5-6 seconds. The derived tables is showing as taken >1 second. WOW

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-18 : 08:53:19

quote:
Originally posted by MrSmallTime

Just in case anyone else follows this post, and going back to my original question. is there more effective way of aggregating from different tables?

Well, the The original code executed on 1700 rows in TblReferral and linked to 4000 - 30,000 (ish) rows in related tables took around 5-6 seconds. The derived tables is showing as taken >1 second. WOW

did you say within a second?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-19 : 09:26:20

yep within a second

MrSmallTime
Starting Member

32 Posts

Posted - 2013-11-19 : 09:32:15

Ah I see meant < not >

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources