March 5, 2009 at 9:31 pm
Hi ,
i need your help to present this master-detail form by tsql,
i have sample data like this;
table a
ida number
1 1
1 2
1 3
2 4
2 5
table b
idb ida
1 1
1 2
and i want my result like this (***)
===============================
ida sum(number)
1 6
2 9
-----------------------------
idb sum
1 15
===============================
i can produce this result by using 'compute' but with add column of table b,like this
===============================
idb ida sum(number)
1 1 6
1 2 9
-----------------------------
idb sum
1 15
===============================
so, is there any way to make the result as i mention above ( at ***)
thanks
sol
March 5, 2009 at 9:55 pm
Yes, it is doable. The code at the bottom produces two result sets with the column names you want to see. I have to ask why you need that format though. You can get the totals you want with a simple summary query grouped on idb and da, like this:
SELECT idb as idb, da as ida, SUM(number) as [sum]
FROM @tableB
JOIN @tableA on da = ida
GROUP BY idb, da
WITH ROLLUP
But if you absolutely must have two result sets with different column names:
----------------------------------------------------------------------------------------
-- two result sets with varying column names
----------------------------------------------------------------------------------------
declare @tableA table (da int, number int)
insert into @tableA
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 4 union all
select 2, 5
declare @tableB table (idb int, ida int)
insert into @tableB
select 1,1 union all
select 1,2
select da as ida,sum(number) as [sum(number)]
from @tableA
group by da
select idb as idb,sum(number) as [sum]
from @tableB
join @tableA on da = ida
group by idb
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 5, 2009 at 10:15 pm
Bob Hovious (3/5/2009)
Yes, it is doable. The code at the bottom produces two result sets with the column names you want to see. I have to ask why you need that format though. You can get the totals you want with a simple summary query grouped on idb and da, like this:SELECT idb as idb, da as ida, SUM(number) as [sum]
FROM @tableB
JOIN @tableA on da = ida
GROUP BY idb, da
WITH ROLLUP
But if you absolutely must have two result sets with different column names:
----------------------------------------------------------------------------------------
-- two result sets with varying column names
----------------------------------------------------------------------------------------
declare @tableA table (da int, number int)
insert into @tableA
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 4 union all
select 2, 5
declare @tableB table (idb int, ida int)
insert into @tableB
select 1,1 union all
select 1,2
select da as ida,sum(number) as [sum(number)]
from @tableA
group by da
select idb as idb,sum(number) as [sum]
from @tableB
join @tableA on da = ida
group by idb
as i said , i can do it , but what i want is something like a report, like i mention above,
it must have detail of table a and sum follow id of table b and go on for next id of table b,
so what you suggested is just two statement and you have two separated results
thanks
sol
March 5, 2009 at 11:10 pm
Yes, I understand completely. I saw what you want in the original post. But, respectfully, that particular format is not a business requirement.
Unfortunately, compute will not do exactly what you want. I stopped and looked back over the BOL text and tested to be sure. To be able to compute on a column it must be included (and therefore displayed) in the first result set returned. You can see why in the execution plan. The closest I could come to producing the last result set was
compute avg(idb), sum([sum]) by idb
However, COMPUTE doesn't let you override the column names with AS, so the column name will be [AVG] (or [MAX] or [MIN]) but never [idb].
Rollup can't be formatted exactly the way you want either. Sorry, but they didn't write it that way.
A UNION of two queries won't work because you can't have a single result set where the column names suddenly change.
My recommendation was simply to do the GROUP BY with ROLLUP. That would give you all the sums, plus any subtotals and a grand total. The data is all there. The application which presents the report can handle the formatting. The code at the bottom would produce a most acceptable display of the totals, very quickly, but you will respond it isn't what you want because it looks slightly different.
At this point you have to choose what you want: quick and efficient retrieval of data, or trying to force SQL to produce an arbitrary format, rather than using it as it is designed. The latter way leads to frustration, poorly performing code, and perhaps madness. 😉
I'm afraid I can't help you any more, but I wish you the best of luck.
-- slightly formatted summary query with rollup
; with totals as
(select idb as idb, da as ida, sum(number) as [sum]
from @tableB
join @tableA on da = ida
group by idb, da
with rollup
)
select isnull(cast(idb as varchar(5)),'*All') as idb
,isnull(cast(ida as varchar(5)),'*All') as ida
,[sum]
from totals
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2009 at 1:09 am
Thanks very much,
And i hope you don't take so serious about what i said :).
The result i expect is only for me to see, and it has nothing to do with some business, i just try my best to see what i can do with current level of my knowledge, and you had showed me a another way, i am very grateful 🙂
I will try your solution and try some more.
Thanks again.
Best regards
sol
March 6, 2009 at 9:20 am
No offense taken, sol 🙂
The only thing I would ask you to consider is SQL's proper function in an overall system. SQL's strength is most definitely NOT formatting of output. Reporting Services or Crystal Reports or any other front end user interface are designed to handle that. SQL is all about storing and retrieving data, and you would profit most from learning how to make it do those functions most efficiently. When you look at something like COMPUTE, read the BOL on it, try some examples, and be aware of what it does and does not do. If something has to be "forced", it probably isn't the right way, or the right tool.
Again, best of luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply