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
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))
-
9 2555
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
-
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.
I don't get it. What do you mean join the two queries? Are you looking for a running total or something?
~~ CK
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.
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
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.
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))
-
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
david thank you very much! you have solved my problem with your super sql skills - much appreciated.
omar.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: B. Fongo |
last post by:
I learned MySQL last year without putting it into action; that is why
I face trouble in formulating my queries. Were it a test, then you
would have passed it, because your queries did help me...
|
by: Dom Boyce |
last post by:
Hi
First up, I am using MS Access 2002.
I have a database which records analyst rating changes for a list of
companies on a daily basis. Unfortunately, the database has been set
up (by my...
|
by: Shahzad |
last post by:
dear respected gurus,
I would like to knew how to apply append,insert query for a self table
where no primary keys issues.
i do have problem say there are 5 rows of single record, this is data...
|
by: rockyptc |
last post by:
greetings. first, i apologize for asking an old question. it appears
that i'm looking for a solution that was already given but it don't
seem to fit my scenario. so thanks for putting up with me.
...
|
by: CSN |
last post by:
Is there much difference between using subqueries and
separating out them into separate queries?
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building...
|
by: cjakeman |
last post by:
Hi,
Solved a little mystery yesterday when I built a form that combined 2
tables with a 1:M relationship and relational integrity. All the
correct data was visible on the form but, if I tried to...
|
by: bobo420 |
last post by:
Hi!
I have 4 tables (table1, table2, table3, table 4)
I need to do select * from all four table and get them sorted
all the tables have field named id, so I figured that's the field I should...
|
by: gubbachchi |
last post by:
Hi,
How to use foreign key with join queries. I have used the query
SELECT first_name,last_name,user_email from Info,Registry WHERE user_id='1';
where user_id = foreign key...
|
by: Rich P |
last post by:
Greetings,
Left Join (outer join) queries are generally for excluding stuff.
select t1.* from tblx t1 Left Join tbly t2 on t1.ID = t2.ID and t1.fld1
= t2.fld1 and t1.fld2 = t2.fld2...
Where...
|
by: manmadhan |
last post by:
hey im new for the database so plz anybody send info abt join queries
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |