October 7, 2011 at 5:10 pm
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.
October 7, 2011 at 6:51 pm
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
October 7, 2011 at 8:42 pm
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
October 8, 2011 at 1:00 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 8, 2011 at 6:45 am
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
October 12, 2011 at 7:35 am
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!
October 12, 2011 at 8:11 am
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! 😛
October 12, 2011 at 8:26 am
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!
October 12, 2011 at 8:38 am
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2011 at 8:41 am
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
October 12, 2011 at 8:48 am
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
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
October 12, 2011 at 8:51 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply