SQL Query Help

zolf
zolf used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.

Author

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
Ensure you’re charging the right price for your IT

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

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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?

Author

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

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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       ????

Author

Commented:
I have tried to provide as much as info,hope it is clear

11
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Author

Commented:
Thanks for your feedbacks. It is not giving me the correct sum,still giving me 80. The correct sum is 38
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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).

Author

Commented:
Thanks for your feedback. I managed to use subquery as you mentioned to achieve my requirement.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Very pleased to hear that. Well done.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial