April 17, 2012 at 11:46 pm
The price sum should be the sum of all the OrderNo records but it's wrong.
SELECT A.[OrderNo]
,COUNT(A.[OrderNo])
,SUM(A.[Price])
,A.[Descr]
,B.[OrderNo]
,COUNT(B.[OrderNo])
,SUM(B.[Price])
,B.[Descr]
FROM [Test].[dbo].[TestTbl] A, [Test].[dbo].[TestTbl] B
WHERE A.[Descr] = 1
AND B.[Descr] = 2
GROUP BY A.[OrderNo],A.[Descr]
, B.[OrderNo],B.[Descr]
How do you write this query join to specify how the tables are joined ie
... FROM tblnameA
INNERJOIN tblnameB ON tblnameA.field = tblnameB.field
April 18, 2012 at 4:29 am
Sorry I don't understand what you need, why is it wrong, what are you expecting to see?
If you can provide DDL's, sample data, expected results and a better explanation this will go a long way to getting help
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 18, 2012 at 4:34 am
The query as you've written it is the old syntax for OUTER APPLY(not INNER JOIN).
Try something like this:
SELECT A.[OrderNo]
,COUNT(A.[OrderNo])
,SUM(A.[Price])
,A.[Descr]
,B.[OrderNo]
,COUNT(B.[OrderNo])
,SUM(B.[Price])
,B.[Descr]
FROM [Test].[dbo].[TestTbl] A
INNER JOIN [Test].[dbo].[TestTbl] B
ON A.[OrderNo] = B.[OrderNo] AND A.[Descr] = B.[Descr]
WHERE A.[Descr] = 1
AND B.[Descr] = 2
GROUP BY A.[OrderNo],A.[Descr]
Not tested but if you tweak it a bit it could work for you.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 8:25 am
Execute your query without GROUP BY to see the values it actually sums.
Probably you have bad join condition as Dwain wrote.
April 18, 2012 at 9:53 am
Your original query created a cross join because you used a comma separated list of tables and did not add the join condition in the where clause. This is one of the main reasons this type of join is not considered best practice.
select *
from tablea, tableb --this is a cross join
select *
from tablea, tableb
where tablea.PK = tableb.FK --this now makes this function like an inner join
Does that help it make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 18, 2012 at 10:27 pm
I have a table that has an OrderNo, Price, and Descr field. The OrderNo is like an acct number. There are multiple records with the same OrderNo. I need to return 2 groups based on the Descr value of 1 or 2. I also need to return the Price sum for each OrderNo. :unsure: It should look like this. Each OrderNo has multiple records. RE the Price sum, it's greater than the actually value. For example OrderNo 001 has 3 records with the price of 30, 30 and 40 which totals 100 but the query returns a greater number.
OrderNo | Price(sum) | Descr
001____|100 _______| 1
002____|142 _______| 1
003____| 88 _______| 2
004____|244 _______| 2
April 18, 2012 at 10:37 pm
can you post your create table statements (DDL), sample data (Hard coded inserts) and your sample output (How you want it to look). if you need some help assembling those items you can read the article i link to in my signature.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 10:44 pm
ReginaR,
Can you also confirm that you've tried some of the suggestions posted earlier and why they are not working for you?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 18, 2012 at 10:49 pm
ReginaR1975 (4/18/2012)
I have a table that has an OrderNo, Price, and Descr field. The OrderNo is like an acct number. There are multiple records with the same OrderNo. I need to return 2 groups based on the Descr value of 1 or 2. I also need to return the Price sum for each OrderNo. :unsure: It should look like this. Each OrderNo has multiple records. RE the Price sum, it's greater than the actually value. For example OrderNo 001 has 3 records with the price of 30, 30 and 40 which totals 100 but the query returns a greater number.OrderNo | Price(sum) | Descr
001____|100 _______| 1
002____|142 _______| 1
003____| 88 _______| 2
004____|244 _______| 2
ok so you gave a third of the requested info in an edit. can you post the create table statements and sample data and the query you are using so we can see what is going wrong? my MindReaderAPI is still vapor ware.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 18, 2012 at 11:00 pm
CREATE TABLE [dbo].[Orders](
[OrderNo] [smallint] NULL,
[Price] [smallmoney] NULL,
[Descr] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,40,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,20,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,50,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,42,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(003,40,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(003,48,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,100,2)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,90,2)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,54,2)
Currently I have 2 different queries. One returns all the records for Descr 1, and the other for Descr 2. The results are correct. I don't think it's possible to combine both groups because the number of records for Descr 1 isn't the same as Descr 2. The fields in the table data returned would need to be like this.
OrderNo1
PriceSum1
Descr1
OrderNo2
PriceSum2
Descr2
April 18, 2012 at 11:22 pm
ReginaR1975 (4/18/2012)
CREATE TABLE [dbo].[Orders]([OrderNo] [smallint] NULL,
[Price] [smallmoney] NULL,
[Descr] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(001,40,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,20,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,30,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,50,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(002,42,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(003,40,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(003,48,1)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,100,2)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,90,2)
INSERT INTO [Test].[dbo].[Orders]([OrderNo],[Price],[Descr])VALUES(004,54,2)
Currently I have 2 different queries. One returns all the records for Descr 1, and the other for Descr 2. The results are correct. I don't think it's possible to combine both groups because the number of records for Descr 1 isn't the same as Descr 2. The fields in the table data returned would need to be like this.
OrderNo1
PriceSum1
Descr1
OrderNo2
PriceSum2
Descr2
im at the house right now so i think this will work but ill test it out in the morning. your output looks like you just need to use a simple sum with a group by.
SELECT OrderNo, SUM(Price), Descr
FROM Orders
GROUP BY OrderNo, Descr
ORDER BY Descr ASC, OrderNo ASC
Since all the entries are in the same table we just need to use an order by to assure the output you want. the different number of records of descr (1) or (2) does not matter since we run the SUM() by ordernumber and descr.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 19, 2012 at 1:07 am
SELECT o.orderno,
Descr1_PriceSum = SUM(CASE WHEN o.Descr=1 THEN o.Price ELSE 0 END),
Descr2_PriceSum = SUM(CASE WHEN o.Descr=2 THEN o.Price ELSE 0 END)
from dbo.orders o
group by o.orderno
ordernoDescr1_PriceSumDescr2_PriceSum
1100,000,00
2142,000,00
388,000,00
40,00244,00
If you want Descr to be dynamic (not known in design time, built from vaues in the table) than it can be done by converting above query to dynamic sql.
April 19, 2012 at 8:47 pm
Thank you 😀
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply