February 5, 2016 at 1:16 pm
The output that I want is listed at the very end.
Can someone help me with a single SQL statement.
I don't mind using one or 2 CTEs ( Common Table Expressions )
The idea is to have a header row that represents the SUM
Then under that the individual rows that make up the SUM
Declare @a table ( p_code varchar(100), CNT INT );
insert INTO @a( p_code, CNT ) VALUES ( '1', 100 );
insert INTO @a( p_code, CNT ) VALUES ( '2', 200 );
Declare @b-2 table ( p_code varchar(100), name varchar(100), CNT INT );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'John' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Peter' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Max' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Ray' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'SAN' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DEY' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'PITS' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DET' , 75 );
p_code namecnt
1100
1 John25
1 Peter25
1 Max25
1 Ray25
2200
2SAN25
2DEY50
2PITS50
2DET75
February 5, 2016 at 1:42 pm
mw112009 (2/5/2016)
The output that I want is listed at the very end.Can someone help me with a single SQL statement.
I don't mind using one or 2 CTEs ( Common Table Expressions )
The idea is to have a header row that represents the SUM
Then under that the individual rows that make up the SUM
Declare @a table ( p_code varchar(100), CNT INT );
insert INTO @a( p_code, CNT ) VALUES ( '1', 100 );
insert INTO @a( p_code, CNT ) VALUES ( '2', 200 );
Declare @b-2 table ( p_code varchar(100), name varchar(100), CNT INT );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'John' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Peter' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Max' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Ray' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'SAN' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DEY' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'PITS' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DET' , 75 );
p_code namecnt
1100
1 John25
1 Peter25
1 Max25
1 Ray25
2200
2SAN25
2DEY50
2PITS50
2DET75
Here's a way of achieving the desired result without using the first table.
SELECT p_code,
name,
SUM(CNT)
FROM @b-2
GROUP BY GROUPING SETS (( p_code), (p_code, name))
ORDER BY p_code, name
It's basically creating an aggregate value for each of the sets defined.
February 10, 2016 at 5:47 am
Luis: It works, However I changed the data somewhat slightly. Now we have 2 more rows in table @a that do not have members in @B.
Take a look at the output listed below. Need your help please.
-----------------------------------------------------------------------------
Declare @a table ( p_code varchar(100), CNT INT );
insert INTO @a( p_code, CNT ) VALUES ( '1', 100 );
insert INTO @a( p_code, CNT ) VALUES ( '2', 200 );
insert INTO @a( p_code, CNT ) VALUES ( '3', 250 );
insert INTO @a( p_code, CNT ) VALUES ( '4', 600 );
Declare @b-2 table ( p_code varchar(100), name varchar(100), CNT INT );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'John' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Peter' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Max' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '1', 'Ray' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'SAN' , 25 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DEY' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'PITS' , 50 );
insert INTO @b-2( p_code, name, CNT ) VALUES ( '2', 'DET' , 75 );
/*
p_code namecnt
1 100
1 John 25
1 Peter 25
1 Max 25
1 Ray 25
2200
2SAN 25
2DEY 50
2PITS 50
2DET 75
3250
4600
*/
February 10, 2016 at 6:05 am
Here is some code that does what you request. However, I do find the requirement to be quite strange - this appears to be a reporting/formatting issue, and those are much better handled by the front end / reporting tool.
SELECT p_code, name, CNT
FROM @b-2
UNION ALL
SELECT p_code, NULL, CNT
FROM @a
ORDER BY p_code, name;
February 10, 2016 at 6:09 am
Avoid using UNION when you don't want to eliminate duplicates. It's additional work which might generate unexpected results.
Use UNION ALL when you only want to get two sets of data to prevent additional sorting.
February 10, 2016 at 7:01 am
Hugo:
Other than using "UNION ALL" I don't see any difference in my code and yours.
Never mind, I agree "UNION ALL" is the best.
February 10, 2016 at 7:53 am
mw112009 (2/10/2016)
Hugo:Other than using "UNION ALL" I don't see any difference in my code and yours.
True. I had not refreshed the browser screen before I posted my reply, so I only saw your reply after I posted mine.
February 10, 2016 at 8:30 am
Instead of scanning the table twice, my suggestion would be to use a GROUP BY WITH ROLLUP and simply sort the output based on GROUPING().
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2016 at 9:08 am
RBAR:
"scanning the table twice" ???.
We have 2 different tables ?
Also it would be nice if you could throw the syntax ( I have not used the ROLLUP before )
February 10, 2016 at 9:20 am
Jeff Moden (2/10/2016)
Instead of scanning the table twice, my suggestion would be to use a GROUP BY WITH ROLLUP and simply sort the output based on GROUPING().
There's two tables and they will always need to be scanned once each.
I did try a GROUP BY WITH ROLLUP version first (amending the original code suggestion Luis posted), and got duplicates. You need to do an outer join to retain the unmatched rows from @a, but then they show up twice - both grouped and ungrouped. So you need to write a WHERE clause to exclude those. But you cannot use GROUPING in the WHERE, so you need to encapsulate in a CTE. Which results in a huge query with some 20-odd lines, and a level of complexity that forces the OP to also add a block comment for the sake of the mental sanity of hirself and/or hir successor.
And then I decided to go with the much simpler and easier to understand 6-line query.
February 10, 2016 at 9:21 am
mw112009 (2/10/2016)
RBAR:"scanning the table twice" ???.
We have 2 different tables ?
Also it would be nice if you could throw the syntax ( I have not used the ROLLUP before )
Luis' original code is a good example of how to use ROLLUP. In your original example, Table @a is actually pretty useless. All it does is contain the sums according to Table @B.
I'm at work right now and the system at my end doesn't let me post code. I'll try to get to this tonight but have a look at Luis' code. You should be able to stamp out the current problem from that.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2016 at 9:36 am
February 10, 2016 at 9:45 am
mw112009 (2/10/2016)
Jeff M:Please refer to Post #1760015.
I changed the code to include 2 extra rows in table @a that are not in table @B.
So you definitely have to use both tables. Can't get away with using just table @B.
Anyway, whenever you have time, I'd like to see how to use the ROLLUP
WITH ROLLUP is sort of equivalent to the GROUPING SETS syntax used in Luis' code. WITH ROLLUP is deprecated; the GROUPING SETS is more versatile and is the recommended syntax going forward.
You can adapt the query posted by Luis to use an outer join between your two tables. Run it without a GROUP BY and you will see that now the two additional rows from @a are retained. Add back the GROUP BY and you will see them duplicated. As I already wrote in my reply to Jeff, there are ways to fix that, but they are rather convoluted.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply