May 2, 2016 at 8:34 pm
Hi All,
I am trying to write a query where I get the total net for all sales order whose sales order number is equal to that of beverages.
Example: salesorder#1 chicken 10$
beverages 5$
salesorder#2 chicken 10$
chips 4$
salesorder#3 beverages 10$
coke 2$
So as per above example I should get 10+5+10+2 = 27.
How can I write the query for this?
May 3, 2016 at 4:04 am
The requirement is not very clear.
We would need the table structure, some sample data and expected results in a easily consumable format.
That would help us test the solution before giving any suggestions to you.
Please check the link in my signature if you are not sure on how to do this.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 3, 2016 at 7:32 am
If I understand correctly, this is a sample code base loosely on AdventureWorks database.
SELECT SUM(LineTotal)
FROM Sales.SalesOrderDetail d
WHERE d.SalesOrderID IN( SELECT i.SalesOrderID
FROM SalesOrderDetail i
WHERE i.Product = 'beverages')
May 3, 2016 at 6:02 pm
SELECT Product Class, sum([Nett])
FROM (([FactSales]
inner join [Date]
on [FactSales].DateKey = [Date].DateKey)
inner join [Product] on [Sales].ProductKey = [Product].ProductKey
inner join [ProductCategory] on [Product].ProductCategoryKey = [ProductCategory].ProductCategoryKey)
inner join [Store] on [Sales].StoreKey = [Store].StoreKey)
where FullDate = '2016-04-27'
and SalesOrderNumber in (select SalesOrderNumber from [NandosDW].[dbo].[FactSales] where ProductClass
= 'Beverages')
and StoreName = 'ABC'
GROUP BY SalesOrderNumber,TransactionDate,ProductClass
ORDER By TransactionDate
I used this query but gives me all the sales order with just beverages in it
May 3, 2016 at 6:16 pm
And what's the problem?
That's what you asked for.
May 3, 2016 at 7:12 pm
Its just giving me total of only beverages in it.
Referring to the example in my first post, its giving beverages (5$) + beverages (10$) = 15$.
I am expecting 27$.
Thanks
May 3, 2016 at 7:43 pm
shivang.rdesai (5/3/2016)
Its just giving me total of only beverages in it.Referring to the example in my first post, its giving beverages (5$) + beverages (10$) = 15$.
I am expecting 27$.
Thanks
Post the query you are running. The query Luis provided should work.
May 3, 2016 at 7:47 pm
Already posted!
May 3, 2016 at 9:28 pm
shivang.rdesai (5/3/2016)
SELECT Product Class, sum([Nett])FROM (([FactSales]
inner join [Date]
on [FactSales].DateKey = [Date].DateKey)
inner join [Product] on [Sales].ProductKey = [Product].ProductKey
inner join [ProductCategory] on [Product].ProductCategoryKey = [ProductCategory].ProductCategoryKey)
inner join [Store] on [Sales].StoreKey = [Store].StoreKey)
where FullDate = '2016-04-27'
and SalesOrderNumber in (select SalesOrderNumber from [NandosDW].[dbo].[FactSales] where ProductClass
= 'Beverages')
and StoreName = 'ABC'
GROUP BY SalesOrderNumber,TransactionDate,ProductClass
ORDER By TransactionDate
I used this query but gives me all the sales order with just beverages in it
You are also grouping on ProductClass, take that out of the GROUP BY. You may also want to pull TransactionDate from the GROUP BY as well.
May 3, 2016 at 10:50 pm
SELECT SalesOrderNumber, sum([Nett])
FROM (([FactSales]
inner join [Date]
on [FactSales].DateKey = [Date].DateKey)
inner join [Product] on [Sales].ProductKey = [Product].ProductKey
inner join [ProductCategory] on [Product].ProductCategoryKey = [ProductCategory].ProductCategoryKey)
inner join [Store] on [Sales].StoreKey = [Store].StoreKey)
where FullDate = '2016-04-27'
and SalesOrderNumber in (select SalesOrderNumber from [NandosDW].[dbo].[FactSales] where ProductClass
= 'Beverages')
and StoreName = 'ABC'
GROUP BY SalesOrderNumber
Used the above and still got the same result 🙁
May 3, 2016 at 11:01 pm
shivang.rdesai (5/3/2016)
SELECT SalesOrderNumber, sum([Nett])FROM (([FactSales]
inner join [Date]
on [FactSales].DateKey = [Date].DateKey)
inner join [Product] on [Sales].ProductKey = [Product].ProductKey
inner join [ProductCategory] on [Product].ProductCategoryKey = [ProductCategory].ProductCategoryKey)
inner join [Store] on [Sales].StoreKey = [Store].StoreKey)
where FullDate = '2016-04-27'
and SalesOrderNumber in (select SalesOrderNumber from [NandosDW].[dbo].[FactSales] where ProductClass
= 'Beverages')
and StoreName = 'ABC'
GROUP BY SalesOrderNumber
Used the above and still got the same result 🙁
You could not possibly get any result from the query above because it contains a syntax error.
_____________
Code for TallyGenerator
May 3, 2016 at 11:15 pm
What syntax error?
I ran the query and it executes !
May 4, 2016 at 6:51 am
shivang.rdesai (5/3/2016)
What syntax error?I ran the query and it executes !
You have an inconsistent number of parenthesis (difference between opening and closing).
You're using Sales instead of FactSales (or the other way around).
I'm pretty sure, your problem comes from the fact that the table FactSales doesn't have a ProductClass column.
Are you using different FactSales tables from different databases?
Please qualify your columns, it's a basic rule to easily debug queries. And use table alias for that.
May 4, 2016 at 8:10 am
First, when you ask for help, please provide the DDL (CREATE TABLE statement) for the table(s) involved, sample data (INSERT INTO statements) for the table(s) involved, the exact expected results you are looking for from the query, and last but not least what you have done so far to answer your own question.
Here is my answer:
-- DDL for the table(s) involved:
create table dbo.SalesOrderLine (
OrderId int,
LineId int,
ProductClass varchar(64),
UnitCost decimal(12,2)
);
-- Sample data for the table:
insert into dbo.SalesOrderLine
values (1,1,'chicken',10.00),
(1,2,'beverages',5.00),
(2,1,'chicken',10.00),
(2,2,'chips',4.00),
(3,1,'beverages',10.00),
(3,2,'coke',2.00);
-- Let's verify the data:
select * from dbo.SalesOrderLine;
-- Answer by OrderId:
with BeverageOrders as (
select
OrderId
from
dbo.SalesOrderLine
where
ProductClass = 'beverages'
)
select
sol.OrderId,
sum(sol.UnitCost)
from
dbo.SalesOrderLine sol
where
exists(select 1 from BeverageOrders bo where bo.OrderId = sol.OrderId)
group by
sol.OrderId;
-- The answer you actually asked for in your OP (original post):
with BeverageOrders as (
select
OrderId
from
dbo.SalesOrderLine
where
ProductClass = 'beverages'
)
select
sum(sol.UnitCost)
from
dbo.SalesOrderLine sol
where
exists(select 1 from BeverageOrders bo where bo.OrderId = sol.OrderId);
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply