2

I have a hard time titling this post.

How can we accomplish the following? 

SELECT 'Body' AS LineType, ItemName, Quantity FROM TableX
UNION ALL
SELECT 'Body' AS LineType, ItemName, Quantity FROM TableY
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, <count of the above two SELECTs>

The result should look like this: 

LineType     ItemName     Qty
Body         Nut          19
Body         Bolt         25
Trailer      NULL         2

Please help; Thank you SOF community.

FMFF
  • 1,652
  • 4
  • 32
  • 62

2 Answers2

2

try a CTE:

;WITH FirstQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM TableX
), SecondQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM TableY
)
SELECT * FROM FirstQ
UNION ALL SELECT * FROM SecondQ
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, ((SELECT COUNT(*) FROM FirstQ) +(SELECT COUNT(*) FROM SecondQ)) AS Quantity

Working sample code:

declare @TableX table (ItemName varchar(5), Quantity int)
INSERT @TableX VALUES ('aa',10)
INSERT @TableX VALUES ('bb',20)

declare @TableY table (ItemName varchar(5), Quantity int)
INSERT @TableX VALUES ('cc',30)

;WITH FirstQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM @TableX
), SecondQ AS 
(
    SELECT 'Body' AS LineType, ItemName, Quantity FROM @TableY
)
SELECT * FROM FirstQ
UNION ALL SELECT * FROM SecondQ
UNION ALL
SELECT 'Trailer' AS LineType, null As ItemName, ((SELECT COUNT(*) FROM FirstQ) +(SELECT COUNT(*) FROM SecondQ)) AS Quantity

OUTPUT:

LineType ItemName Quantity
-------- -------- -----------
Body     aa       10
Body     bb       20
Body     cc       30
Trailer  NULL     3

(4 row(s) affected)
KM.
  • 101,727
  • 34
  • 178
  • 212
  • This works. I'll mark this as an answer as soon as SOF allows me. Thank you for the swift response. – FMFF Aug 11 '11 at 21:00
0
declare @TableX table (ItemName varchar(10),Quantity int)
declare @TableY table (ItemName varchar(10),Quantity int)

insert @tableX VALUES('door',0)
insert @tableX VALUES('window',10)
insert @tableX VALUES('wheel',5)
insert @tableX VALUES('brake',7)
insert @tableX VALUES('handle',3)
insert @tableX VALUES('lamp',207)

insert @tableY VALUES('Ydoor',0)
insert @tableY VALUES('Ywindow',10)
insert @tableY VALUES('Ywheel',5)
insert @tableY VALUES('Ybrake',7)
insert @tableY VALUES('Yhandle',3)
insert @tableY VALUES('Ylamp',207)

SELECT 'Body' as LineType, ItemName, Sum(Quantity) qty, Count(*) [count] FROM
(
SELECT ItemName, Quantity FROM @TableX
UNION
SELECT ItemName, Quantity FROM @TableY
) u

GROUP BY ItemName WITH ROLLUP
Chains
  • 12,541
  • 8
  • 45
  • 62
  • several errors: need to remove extra `UNION ALL`, need to remove the `as LineType` within the `GROUP BY`. After that, you'll still get: `Each GROUP BY expression must contain at least one column that is not an outer reference.` – KM. Aug 11 '11 at 20:57
  • @KM -- thanks for the proof read. Too lazy to test. See revised answer. – Chains Aug 11 '11 at 21:33
  • 1
    pretty is in the eye of the beholder. your version does not provide a row with a LineType='Trailer', but instead generates that row as LineType='Body'. I bet the OP needs that 'Trailer' value for some processing, and having a 'Body' value will not meet their needs. – KM. Aug 12 '11 at 12:32