Summarize Results from two tables, using one query.

Question

Summarize Results from two tables, using one query.

cyndi_kemp

Newbie

Points: 9
More actions
October 7, 2011 at 5:10 pm

#249641

Looking for summary results from two different queries.
Getting results:
strLocationNameTrunksLines
Location3096
Location3240
Location2020
Main Hub 01034
Main Hub 480
Want Results:
strLocationNameTrunksLines
Location32496
Location2020
Main Hub 481034
QUERY:
SELECT tblUCSU.strLocationName, COUNT(dbo.tblTACSU.Pen) AS Trunks, 0 AS Lines
FROM dbo.tblTACSU INNER JOIN
dbo.tblUCSU AS tblUCSU ON dbo.tblTACSU.adjPen2 = tblUCSU.strPen
GROUP BY tblUCSU.strLocationName
UNION
SELECT tblUCSU.strLocationName, 0 AS Trunks, COUNT(dbo.tblSCSU.Extension) AS Lines
FROM dbo.tblSCSU INNER JOIN
dbo.tblUCSU AS tblUCSU ON dbo.tblSCSU.adjPen2 = tblUCSU.strPen
GROUP BY tblUCSU.strLocationName
I am missing something here, just can't figure out what it is. Any help is appreciated. Thanks.

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To help those who want to help you with a tested solution, please, please post the table definition, sample data both in a easily consumable manner. To do so, click on the first link in my signature block, read the article. Note the article contains sample T-SQL to allow you to post what I have requested in a short time period

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You don't want the UNION you want 1 query with 2 COUNTs like this:

SELECT

tblUCSU.strLocationName,

COUNT(dbo.tblTACSU.Pen) AS Trunks,

COUNT(dbo.tblSCSU.Extension) AS Lines

FROM

dbo.tblTACSU

INNER JOIN dbo.tblUCSU AS tblUCSU

ON dbo.tblTACSU.adjPen2 = tblUCSU.strPen

GROUP BY

tblUCSU.strLocationName

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Jack Corbett (10/7/2011)
You don't want the UNION you want 1 query with 2 COUNTs like this:
SELECT
tblUCSU.strLocationName,
COUNT(dbo.tblTACSU.Pen) AS Trunks,
COUNT(dbo.tblSCSU.Extension) AS Lines
FROM
dbo.tblTACSU
INNER JOIN dbo.tblUCSU AS tblUCSU
ON dbo.tblTACSU.adjPen2 = tblUCSU.strPen
GROUP BY
tblUCSU.strLocationName

You missed a table from your FROMlist, Jack.

There are two child tables to join to dbo.tblUCSU and the cardinality is unknown, so I'd roll them up separately then join back to parent, like so:

SELECT

u.strLocationName,

SUM(ta.Trunks) AS Trunks,

SUM(s.Lines) AS Lines

FROM dbo.tblUCSU u

INNER JOIN (SELECT adjPen2, Trunks = COUNT(*) FROM dbo.tblTACSU GROUP BY adjPen2) ta ON ta.adjPen2 = u.strPen

INNER JOIN (SELECT adjPen2, Lines = COUNT(*) FROM dbo.tblSCSU GROUP BY adjPen2) s ON s.adjPen2 = u.strPen

GROUP BY u.strLocationName

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

That's what I get for doing it after my bedtime. It's also a reason I hate table names like that. What the heck is an ACSU or SCSU or R2D2?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

cyndi_kemp Newbie Points: 9 More actions · Answer 5

Thank you for the quick response. I tried the query you wrote, and it runs with no error, however it returns no results either. I am not sure I understand it enough to figure out what isn't working. Here is my table layout (minus extraneous fields).

tblUCSU

iIndex, strPen, strLocation

tblSCSU

iIndex, Extension, adjPen2

tblTACSU

iIndex, CircuitTrunk, adjPen2

The count of items in tblSCSU will give me the number of Lines.

The count of items in tblTACSU will give me the number of Trunks.

I need both of these in a summary by Location, which is found in tblUCSU. The link is between tblUCSU.strPen, and tblSCSU.adjPen2, tblTACSU.adjPen2.

Thanks again for your help!

Davin21 SSC Eights! Points: 860 More actions · Answer 6

I think this should do the trick?

SELECT

table1.strlocation

,COUNT(table2.extension) as number_lines

,COUNT(table3.circuitTrunk) as number_trunks

FROM tblUCSU table1

INNER JOIN tblSCSU table2 on table2.adjPen2= table1.strPen

INNER JOIN tblTACSU table3 on table3.adjPen2= table1.strPen

GROUP BY table1.strlocation

If there are records in table 1 which do not exist in either tables 2 or 3 then change the Inner Join to a Left Outer Join instead.

Those table names are a bit of a nightmare - I'd be throwing typos all over the shop with those! 😛

cyndi_kemp Newbie Points: 9 More actions · Answer 7

That was it, changing the join to a left outer join did the trick. Thanks so much for your help. Sorry about the table names, come from the raw data files I get. I thought about changing them for this post, but once I start changing things, I have to remember each thing I changed, so figured it'd be best to leave as is. Thanks again!

Phil Parkin SSC Guru Points: 246970 More actions · Answer 8

cyndi_kemp (10/12/2011)
That was it, changing the join to a left outer join did the trick. Thanks so much for your help. Sorry about the table names, come from the raw data files I get. I thought about changing them for this post, but once I start changing things, I have to remember each thing I changed, so figured it'd be best to leave as is. Thanks again!

Maybe you can't change the underlying table names, but you may find it easier if you use aliases:

select Cust.x, Cust.y

from vRkNUh4m Cust

etc etc

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76990 More actions · Answer 9

You have to be careful with the joins, if either of the tables has a one-to-many relationship. You could potentially be double-counting some of the records I would use correlated subqueries instead.

SELECT

table1.strlocation

,( SELECT COUNT(table2.extension)

FROM tblSCSU table2

WHERE table2.adjPen2= table1.strPen

) as number_lines

,( SELECT COUNT(table3.circuitTrunk)

FROM tblTACSU table3

WHERE table3.adjPen2= table1.strPen

) as number_trunks

FROM tblUCSU table1

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

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

Good catch, Drew. I was about to point it out with evidence:

USE tempdb

GO

CREATE TABLE tblUCSU (strlocation VARCHAR(10), strPen INT)

INSERT INTO tblUCSU (strlocation, strPen)

SELECT 'London', 1 UNION ALL

SELECT 'New York', 2

CREATE TABLE tblSCSU (adjPen2 INT, extension VARCHAR(10))

INSERT INTO tblSCSU (adjPen2, extension)

SELECT 1, 'Ext 1' UNION ALL

SELECT 1, 'Ext 2' UNION ALL

SELECT 1, 'Ext 3'

CREATE TABLE tblTACSU (adjPen2 INT, circuitTrunk VARCHAR(10))

INSERT INTO tblTACSU (adjPen2, circuitTrunk)

SELECT 1, 'CT 1'

SELECT

table1.strlocation

,COUNT(table2.extension) as number_lines

,COUNT(table3.circuitTrunk) as number_trunks

FROM tblUCSU table1

INNER JOIN tblSCSU table2 on table2.adjPen2= table1.strPen

INNER JOIN tblTACSU table3 on table3.adjPen2= table1.strPen

GROUP BY table1.strlocation

SELECT

table1.strlocation

,table2.extension

,table3.circuitTrunk

FROM tblUCSU table1

INNER JOIN tblSCSU table2 on table2.adjPen2= table1.strPen

INNER JOIN tblTACSU table3 on table3.adjPen2= table1.strPen

DROP TABLE tblUCSU

DROP TABLE tblSCSU

DROP TABLE tblTACSU

^{“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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 11

drew.allen (10/12/2011)
You have to be careful with the joins, if either of the tables has a one-to-many relationship. You could potentially be double-counting some of the records I would use correlated subqueries instead.
SELECT
table1.strlocation
,( SELECT COUNT(table2.extension)
FROM tblSCSU table2
WHERE table2.adjPen2= table1.strPen
) as number_lines
,( SELECT COUNT(table3.circuitTrunk)
FROM tblTACSU table3
WHERE table3.adjPen2= table1.strPen
) as number_trunks
FROM tblUCSU table1
Drew

I wouldn't use the correlated sub-queries as they will perform poorly. The derived tables that Chris proposed in his first post is likely the best solution to this problem (or use CTE's if you find them easier to read.).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question