Trying to write a query which fetches total net for sales order that are having same sales order as bevergaes

  • 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?

  • 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.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • 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')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • And what's the problem?

    That's what you asked for.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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

  • 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.

  • Already posted!

  • 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.

  • 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 🙁

  • 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

  • What syntax error?

    I ran the query and it executes !

  • 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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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