how to join 2 sql queries

[omar999]

hi guys

im a little stuck - how would I join this query

select *,isnull(price_band_1,0)+isnull(price_band_2,0)+isnull(price_band_3,0)+isnull(price_band_4,0)+isnull(price_band_5,0) as pricesum 
from TESTCA_Specials WHERE ID IN (1, 3, 5, 9, 13, 19, 31)

to

select totalsum = SUM(totalsum) from
(Select isnull(price_band_1,0)+isnull(price_band_2,0)+isnull(price_band_3,0)+isnull(price_band_4,0)+isnull(price_band_5,0) 
as totalsum from TESTCA_Specials WHERE ID IN (1, 3, 5, 9, 13, 19, 31) ) x

? any ideas
thanks

[David Gluth]

You want to use a derived table - the result will look something like this

select *,isnull(price_band_1,0)+isnull(price_band_2,0)+isnull(price_band_3,0)+isnull(price_band_4,0)+isnull(price_band_5,0) as pricesum  , derivedtbl1.totalsum
from TESTCA_Specials WHERE ID IN (1, 3, 5, 9, 13, 19, 31) CROSS JOIN
 (SELECT select totalsum = SUM(totalsum) from 
(Select isnull(price_band_1,0)+isnull(price_band_2,0)+isnull(price_band_3,0)+isnull(price_band_4,0)+isnull(price_band_5,0)  
as totalsum from TESTCA_Specials WHERE ID IN (1, 3, 5, 9, 13, 19, 31) )) AS Derivedtbl_1

[omar999]

hi david - thanks for your reply


I've tried your suggestion but I'm getting the following error?
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CROSS'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

please advise

Omar.

[ck9663]

I don't get it. What do you mean join the two queries? Are you looking for a running total or something?

~~ CK

[omar999]

hi CK - i basically want to ascertain both query results.. dont need a total of both queries comnbined but just the results of both queries if that makes sense..

i know you can use the UNION all to join 2 sql queries but I couldnt quite work out how ...

thanks in advance
Omar.

[David Gluth]

Join

Hey Omar,

After re-reading your post I see that you are not really trying to join two different tables. Your first query lists the various columns and summaries the 5 price bands for the given keys, while the second query give a total of all price bands for the give keys. I am not clear about what you would like to have joined. Do you want each row of the send query to also include the total from the first query? I would do that this way:

SELECT     TESTCA_Specials.ID, TESTCA_Specials.OtherField, ISNULL(TESTCA_Specials.Price_Band_1, 0) AS Price1, ISNULL(TESTCA_Specials.price_band_2, 0) 
                      AS Price2, ISNULL(TESTCA_Specials.price_band_3, 0) AS Price3, ISNULL(TESTCA_Specials.price_band_4, 0) AS Price4, 
                      ISNULL(TESTCA_Specials.price_band_5, 0) AS Price5, derivedtbl_1.totalsum
FROM         TESTCA_Specials CROSS JOIN
                          (SELECT     SUM(totalsum) AS totalsum
                            FROM          (SELECT     ISNULL(Price_Band_1, 0) + ISNULL(price_band_2, 0) + ISNULL(price_band_3, 0) + ISNULL(price_band_4, 0) 
                                                                           + ISNULL(price_band_5, 0) AS totalsum
                                                    FROM          TESTCA_Specials AS TESTCA_Specials_1
                                                    WHERE      (ID IN (1, 3, 5, 9, 13, 19, 31))) AS derivedtbl_2) AS derivedtbl_1

Resulting in

ID OtherField Price1 Price2 Price3 Price4 Price5 totalsum
1 one 1 2 3 4 5 30
3 three 1 2 3 4 5 30

Dave

[omar999]

david

thank you for your reply. it seems you have hit the nail on its head. your suggestion seems perfect to what I'm trying to achieve. although it just needs a small tweak..

working query

use prices
SELECT ID, ISNULL(Price_Band_1, 0) AS Price1, ISNULL(price_band_2, 0) AS Price2, ISNULL(price_band_3, 0) AS Price3, 
ISNULL(price_band_4, 0) AS Price4, ISNULL(price_band_5, 0) AS Price5, derivedtbl_1.totalsum FROM TESTCA_Specials
CROSS JOIN
(SELECT SUM(totalsum) AS totalsum 
FROM (SELECT ISNULL(Price_Band_1, 0) + ISNULL(price_band_2, 0) + ISNULL(price_band_3, 0) + ISNULL(price_band_4, 0) 
+ ISNULL(price_band_5, 0) AS totalsum FROM TESTCA_Specials WHERE ID IN (1,2,3)) AS derivedtbl_2) AS derivedtbl_1 
WHERE ID IN (1,2,3)

outputs
ID Price1 Price2 Price3 Price4 Price5 totalsum
1 249 139 139 0 0 1511
2 149 139 139 0 0 1511
3 279 139 139 0 0 1511

as you can see the total sum is of all 3 records - the totalsum column should represent total of each individual record and not all 3 records. Is there a way to do this?

thanks in advance
Omar.

[David Gluth]

That is a little lesss complicated:

SELECT     ID, OtherField, ISNULL(Price_Band_1, 0) AS Price1, ISNULL(price_band_2, 0) AS Price2, ISNULL(price_band_3, 0) AS Price3, ISNULL(price_band_4, 0) 
                      AS Price4, ISNULL(price_band_5, 0) AS Price5, ISNULL(Price_Band_1, 0) + ISNULL(price_band_2, 0) + ISNULL(price_band_3, 0) 
                      + ISNULL(price_band_4, 0) + ISNULL(price_band_5, 0) AS RowTotal
FROM         TESTCA_Specials
WHERE     (ID IN (1, 2, 3))

[David Gluth]

Using the second query

Here is an example using the second query and drived table is you feel that is necessary

SELECT     TESTCA_Specials.ID, TESTCA_Specials.OtherField, ISNULL(TESTCA_Specials.Price_Band_1, 0) AS Price1, ISNULL(TESTCA_Specials.price_band_2, 0) 
                      AS Price2, ISNULL(TESTCA_Specials.price_band_3, 0) AS Price3, ISNULL(TESTCA_Specials.price_band_4, 0) AS Price4, 
                      ISNULL(TESTCA_Specials.price_band_5, 0) AS Price5, ISNULL(derivedtbl_1.RowTotal, 0) AS RowTotal
FROM         TESTCA_Specials LEFT OUTER JOIN
                          (SELECT     ID, ISNULL(Price_Band_1, 0) + ISNULL(price_band_2, 0) + ISNULL(price_band_3, 0) + ISNULL(price_band_4, 0) + ISNULL(price_band_5, 0)
                                                    AS RowTotal
                            FROM          TESTCA_Specials AS TESTCA_Specials_1) AS derivedtbl_1 ON TESTCA_Specials.ID = derivedtbl_1.ID

[omar999]

david thank you very much! you have solved my problem with your super sql skills - much appreciated.

omar.