SQL Query Help

Hello there,

Can somebody please help me to rewrite this query to give me the sum of the total sales of product and discount in each branch office. Now the records returned are giving me for each day of the month. I want to sum it to 1 month.

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount AS Dis2
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.DeliveryOrderDetail.quantity,
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Open in new window

1
zolfAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try change to:

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    SUM(dbo.DeliveryOrderDetail.quantity )                AS SalesQty,
    SUM(dbo.DeliveryOrderDetail.quantityDiscount )        AS Dis1,
    SUM(dbo.DeliveryOrderDetail.supplierQuantityDiscount)  AS Dis2
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name

Open in new window

?
PortletPaulEE Topic AdvisorCommented:
SELECT
    ... ,
    dbo.Receipt.receiptDate,
   ...
 WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    ...,
    dbo.Receipt.receiptDate, ...
 
Remove the date from the select and group by clauses (in bold)
You are currently asking for rows for EACH DATE, so stop doing that and the number of rows will reduce.

-----
If you must have a date column you could use a constant for the starting date or ending date or both e.g.

SELECT
    ... ,
    dateadd(day,0,'20150622') as PeriodStartDate,
    ...

 WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    ...

This will still reduce the number of rows from your current query.
zolfAuthor Commented:
Thanks guys for your feedbacks!!

Ryan Chong:
your query was not helpful.

Paul Maxwell:
your explanation was helpful for me to understand the core principle and then I managed to make some changes to my query and it brought me closer to my requirement but I have issue with one column. That is the Discount columns. please see the screenshot and my query changes.

SELECT
    --dbo.Receipt.transactionNumber,
    --dbo.Receipt.receiptDate,
    dbo.Tafsil.description as ProductName,
    dbo.Branch.name as BranchOffice,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyInclDis,
    --dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    --dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    --dbo.DeliveryOrderDetail.supplierQuantityDiscount AS Dis2,
    SUM(DeliveryOrderDetail.quantityDiscount+DeliveryOrderDetail.supplierQuantityDiscount) as TotalDiscount
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    --dbo.Receipt.transactionNumber,
    --dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name
    --dbo.DeliveryOrderDetail.quantity,
    --dbo.DeliveryOrderDetail.quantityDiscount,
    --dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Open in new window

1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
PortletPaulEE Topic AdvisorCommented:
Zolf,
You are expecting us to know/understand your tables & fields.
That is very hard for us (probably impossible without your help).

You are using this:
SUM( DeliveryOrderDetail.quantityDiscount
       +DeliveryOrderDetail.supplierQuantityDiscount  ) as TotalDiscount

That is giving incorrect answers, but what IS the correct result for that column?
(we don't know this only you do)

Using a very small data example,how would you (manually) calculate the correct result?
zolfAuthor Commented:
Thanks Paul for your feedbacks, I have tried to product screenshot of the tables to help you understand my requirement and hence help me to achieve my goal.

1
2
zolfAuthor Commented:
and this is the query which gives me the records for the above screenshot i.e I have not added them all up.

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description as productName,
    dbo.Branch.name as branchoffice,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount AS Dis2,
   (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DiscountSum
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.DeliveryOrderDetail.quantity,
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Open in new window

PortletPaulEE Topic AdvisorCommented:
Excellent information, thank you, but a db diagram does not tell us what YOU consider to be CORRECT.

Take some values from those quantity columns and manually show us what the correct arithmetic is.

E.g.
Qtya. qtyb. expected calculation
10        5       ????
zolfAuthor Commented:
I have tried to provide as much as info,hope it is clear

11
PortletPaulEE Topic AdvisorCommented:
well if you only wanted the SUM( "this column" )

why do you have SUM( "this column" + "that column" ) ?

Change:
    SUM(DeliveryOrderDetail.quantityDiscount+DeliveryOrderDetail.supplierQuantityDiscount) as TotalDiscount

to
 , SUM(DeliveryOrderDetail.quantityDiscount)
, SUM(DeliveryOrderDetail.supplierQuantityDiscount)

now see if one of these 2 columns produce the correct results
zolfAuthor Commented:
Thanks for your feedbacks. It is not giving me the correct sum,still giving me 80. The correct sum is 38
PortletPaulEE Topic AdvisorCommented:
If the issue isn't related to SUM( "this column" + "that column" )  then I think your problem is that you are attempting to aggregate AND join many tables at the same time. Remember that when you join tables you MULTIPLY THE NUMBER OF ROWS so if A row has 5, but the number of rows gets multiplied by 10 then the SUM() of that column will go from 5 to 50.

So, you may need to use a subquery instead of a direct join on table DeliveryOrderDetail, and that subquery should perform the wanted summation. This subquery may involve other tables too. Please note this is something I cannot test as I do not have reusable data per table, and I cannot spend the time needed to reverse engineer those images into relevant tables (and this might not be possible anyway).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Thanks for your feedback. I managed to use subquery as you mentioned to achieve my requirement.
PortletPaulEE Topic AdvisorCommented:
Very pleased to hear that. Well done.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.