T-SQL question
-
I have 2 tables A and B
A:
Date Col1 9/1/2005 S1 9/1/2005 S2 9/1/2005 S3 9/2/2005 S4 9/2/2005 S5 9/2/2005 S6 9/2/2005 S7 9/3/2005 S8 9/3/2005 S9 9/3/2005 S10 B:
Date Type cnt 9/1/2005 A 10 9/1/2005 B 12 9/1/2005 C 2 9/2/2005 A 3 9/2/2005 B 7 9/2/2005 C 5 9/3/2005 A 34 9/3/2005 B 5 9/3/2005 C 5 SELECT Date,COUNT(*) FROM A GROUP BY Date
Date Count 9/1/2005 3 9/2/2005 4 9/3/2005 3 SELECT Date,SUM(cnt) FROM B GROUP BY Date
Date Count 9/1/2005 22 9/2/2005 10 9/3/2005 39 But i need result from 2 tables as follows
Date A Count B Sum 9/1/2005 3 22 9/2/2005 4 10 9/3/2005 3 39 Is there a way to do that? Any help please....
Thanks.
-
Something like this
select [Date],
min(case type when 'A Count' then [count]end) as 'A Count',
min(case type when 'B Sum' then [count]end) as 'B Sum',
From (
SELECT 'A Count' as Type, [Date],COUNT(*) [count]
FROM A GROUP BY Date
union
SELECT 'B Sum', Date,SUM(cnt)
FROM B GROUP BY Date
) as DerivedTable1
group by [Date]
-
It worked, Thank you very much.
-
Why not use 2 derived tables and full join on the date?? That way you can always add more columns when needed. Maybe this is just me but I find that easier to understand than the pivot table option (but I don't like those, so maybe I'm buyest
) -
Indeed, but who knows why they would want to aggregate data from 2 different tables and present them in 1 recordset.
Probably would have been too much trouble to ask why they were doing it this way.
Sometimes the gui guys are too lazy/incapable of merging 2 recordsets into 1 datagrid. so they push the work down to the db.
-
Programmers, lazy??
Not possible
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply