June 17, 2008 at 5:24 am
Hi all!,
First post, so I hope I posted it in the right section...
The query below is working fine, but I started to think if this really was the most efficient way to do it?
Every "Org" can only appear once per Year and Month
Select
SUM(ISNULL(R1.Users,0))As 'Col1',
SUM(ISNULL(R2.Users,0))As 'Col2',
SUM(ISNULL(R3.Users,0))As 'Col3'
From Users U
Left Join (Select * from Report) R1 On U.UserAlias = R1.UserAlias AND
U.Organization = 'Org1' AND
R1.Year = 2008 And R1.Month = 4
Left Join (Select * from Report) R2 On U.UserAlias = R2.UserAlias AND
U.Organization = 'Org2' AND
R2.Year = 2008 And R2.Month = 4
Left Join (Select * from Report) R3 On U.UserAlias = R3.UserAlias AND
U.Organization = 'Org3' AND
R3.Year = 2008 And R3.Month = 4
Example of wanted output:
Col1 Col2 Col3
11 22 33
Alternatives on better solutions are appreciated.
June 17, 2008 at 5:39 am
Since you're using SQL 2005, look up PIVOT in books online.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2008 at 9:05 am
Can you please post your DDL and some sample data?
That will make it a lot easier to answer your question. 😉
June 18, 2008 at 9:44 am
Yes of course 🙂
Sorry about that
Here is a small example:
CREATE TABLE #Users([UserAlias] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL,
[FName] [nchar](20) COLLATE Latin1_General_CI_AI NULL,
[LName] [nchar](20) COLLATE Latin1_General_CI_AI NULL,
[Organization] [nchar](20) COLLATE Latin1_General_CI_AI NULL)
INSERT INTO #Users (UserAlias,FName,LName ,Organization) VALUES ('doej','John','Doe','Org1')
INSERT INTO #Users (UserAlias,FName,LName ,Organization) VALUES ('clarkc','Clark','Jones','Org2')
INSERT INTO #Users (UserAlias,FName,LName ,Organization) VALUES ('holmesk','Kate','Holmes','Org3')
CREATE TABLE #Report([UserAlias] [nchar](10) COLLATE Latin1_General_CI_AI NOT NULL,
[Year] [int] NOT NULL,[Month] [int] NOT NULL,[Users] [int] NULL)
INSERT INTO #Report (UserAlias,[Year],[Month],[Users]) VALUES ('doej',2008,4,755)
INSERT INTO #Report (UserAlias,[Year],[Month],[Users]) VALUES ('clarkc',2008,4,10)
INSERT INTO #Report (UserAlias,[Year],[Month],[Users]) VALUES ('holmesk',2008,4,555)
INSERT INTO #Report (UserAlias,[Year],[Month],[Users]) VALUES ('doej',2008,5,755)
INSERT INTO #Report (UserAlias,[Year],[Month],[Users]) VALUES ('calkc',2008,5,10)
Select
SUM(ISNULL(R1.Users,0)) As 'Org1',
SUM(ISNULL(R2.Users,0)) As 'Org2',
SUM(ISNULL(R3.Users,0)) As 'Org3'
From #Users U
Left Join (Select * from #Report) R1 On U.UserAlias = R1.UserAlias AND
U.Organization = 'Org1' AND
R1.Year = 2008 And R1.Month = 4
Left Join (Select * from #Report) R2 On U.UserAlias = R2.UserAlias AND
U.Organization = 'Org2' AND
R2.Year = 2008 And R2.Month = 4
Left Join (Select * from #Report) R3 On U.UserAlias = R3.UserAlias AND
U.Organization = 'Org3' AND
R3.Year = 2008 And R3.Month = 4
Drop table #Users
Drop table #Report
Hmm PIVOT you say.. Never tried that. Still thinking SQL 2000 I guess 😉
June 18, 2008 at 10:17 am
Try:
SELECT [Year], [Month] ,[Org1],[Org2],[Org3]
FROM
(SELECT U.Organization, R.Users , [Year], [Month]
FROM #Report R INNER JOIN #USERS U ON U.UserAlias = R.UserAlias) AS src
PIVOT ( SUM (Users) FOR Organization IN ( [Org1],[Org2],[Org3])) AS Pivoted
* Noel
June 18, 2008 at 10:18 am
How about this?
;WITH OrganizationUsers AS
(
SELECT U.Organization
,R.Users
,R.[Month]
,R.[Year]
FROM #Users U
JOIN #Report R ON U.UserAlias = R.UserAlias
)
SELECT [Year]
, [Month]
, [Org1]
, [Org2]
, [Org3]
FROM OrganizationUsers
PIVOT( SUM(Users) FOR Organization IN ([Org1], [Org2], [Org3])) AS OrganizationPivot
GROUP BY [Org1]
, [Org2]
, [Org3]
, [Year]
, [Month]
June 18, 2008 at 11:03 am
Joe Celko (6/18/2008)
You used singular table names but they don't seem to have only one row. You used reserved words for column names or vague useless names like "organization" for attribute. You split dates apart instead of keeping them atomic. Let's use "foobar" for the missing properties or root attributes on the data elements until you have time to get up to ISO-11179 Standards.Here is a cleaned up version of the original:
SELECT
SUM(ISNULL(R1.users, 0)) AS col1,
SUM(ISNULL(R2.users, 0)) AS col2,
SUM(ISNULL(R3.users, 0)) AS col3
FROM Users AS U
LEFT OUTER JOIN
(SELECT * FROM Reports) AS R1
ON U.user_alias = R1.user_alias
AND U.organization_foobar = 'Org1'
AND R1.foobar_year = 2008
AND R1.foobar_month = 4
LEFT OUTER JOIN
(SELECT * FROM Reports) AS R2
ON U.user_alias = R2.user_alias
AND U.organization_foobar = 'Org2'
AND R2.foobar_year = 2008
AND R2.foobar_month = 4
LEFT OUTER JOIN
(SELECT * FROM Reports) AS R3
ON U.user_alias = R3.user_alias
AND U.organization_foobar = 'Org3'
AND R3.foobar_year = 2008
AND R3.foobar_month = 4 ;
Let's assume that you correct the split date and some other things about the missing DDL, then you should be able to write this and have it run 1-3 orders of magnitude faster:
SELECT SUM (CASE WHEN U.organization_foobar = 'Org1' THEN 1 ELSE 0 END) AS org1_cnt,
SUM (CASE WHEN U.organization_foobar = 'Org2' THEN 1 ELSE 0 END) AS org2_cnt,
SUM (CASE WHEN U.organization_foobar = 'Org3' THEN 1 ELSE 0 END) AS org3_cnt
FROM Users AS U, Reports AS R
WHERE R.foobar_date BETWEEN '2008-04-01' AND '2008-04-30'
AND U.user_alias = R2.user_alias;
Erm ...... Someone is preaching "standards" and is not using "ANSI" Joins ?
* Noel
June 18, 2008 at 11:50 am
Actually, Joe used ANSI joins. The second query is a cross-join with two Where statements. That's standard.
(Have to admit, I don't like the layout he used, but that's got nothing to do with published standards.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 11:55 am
Question: What's with all of the Left Join (Select * from #Report) ?
Is that any different than doing a join like this:
Select
SUM(ISNULL(R1.Users,0)) As 'Org1',
SUM(ISNULL(R2.Users,0)) As 'Org2',
SUM(ISNULL(R3.Users,0)) As 'Org3'
From #Users U
Left Join #Report R1 On U.UserAlias = R1.UserAlias AND
U.Organization = 'Org1' AND
R1.Year = 2008 And R1.Month = 4
Left Join #Report R2 On U.UserAlias = R2.UserAlias AND
U.Organization = 'Org2' AND
R2.Year = 2008 And R2.Month = 4
Left Join #Report R3 On U.UserAlias = R3.UserAlias AND
U.Organization = 'Org3' AND
R3.Year = 2008 And R3.Month = 4
June 18, 2008 at 11:58 am
The second query is a cross-join with two Where statements. That's standard.
I beg to differ. A comma is NOT an ANSI join, CROSS JOIN would be!
* Noel
June 18, 2008 at 12:00 pm
GSquared (6/18/2008)
Actually, Joe used ANSI joins. The second query is a cross-join with two Where statements. That's standard.(Have to admit, I don't like the layout he used, but that's got nothing to do with published standards.)
I was under the impression that putting the join criteria in the WHERE clause is a violation of said ANSI standard. After all (assuming you fix the typo here):
U.user_alias = R.user_alias;--<--R2 is an invalid alias
would be a join criterion wouldn't it?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 18, 2008 at 12:02 pm
Matt Miller (6/18/2008)
GSquared (6/18/2008)
Actually, Joe used ANSI joins. The second query is a cross-join with two Where statements. That's standard.(Have to admit, I don't like the layout he used, but that's got nothing to do with published standards.)
I was under the impression that putting the join criteria in the WHERE clause is a violation of said ANSI standard. After all (assuming you fix the typo here):
U.user_alias = R.user_alias;--<--R2 is an invalid aliaswould be a join criterion wouldn't it?
Totally agree!
* Noel
June 18, 2008 at 3:27 pm
Thank you all for your suggestions and tips!
ggraber: Yes you are right, don't know why I used the select * :hehe:
I think I will go for the PIVOT suggested by noeld which seems more efficient.
And yes, maybe I need to take a closer look at the standards...
June 18, 2008 at 10:43 pm
They are both ANSI joins. The commas are the Old (SQL 89) syntax, the "JOIN"'s are the New (SQL 92 and later) syntax. OK, New-er syntax.
I much prefer the newer syntax, I think using the old syntax makes you look like an ossified brachiosaurus.
And, yes Joe, I did read your books (well some of them, I have a lot of reading to do). They are among my very favorites. Thanks for writing them.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply