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!
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.
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 ;
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.
... ,
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.
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.
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 ;
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.quanti
+DeliveryOrderDetail.suppl
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?
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.quanti
+DeliveryOrderDetail.suppl
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?
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.
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 ;
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 ????
Take some values from those quantity columns and manually show us what the correct arithmetic is.
E.g.
Qtya. qtyb. expected calculation
10 5 ????
well if you only wanted the SUM( "this column" )
why do you have SUM( "this column" + "that column" ) ?
Change:
SUM(DeliveryOrderDetail.qu
to
, SUM(DeliveryOrderDetail.qu
, SUM(DeliveryOrderDetail.su
now see if one of these 2 columns produce the correct results
why do you have SUM( "this column" + "that column" ) ?
Change:
SUM(DeliveryOrderDetail.qu
to
, SUM(DeliveryOrderDetail.qu
, SUM(DeliveryOrderDetail.su
now see if one of these 2 columns produce the correct results
Thanks for your feedbacks. It is not giving me the correct sum,still giving me 80. The correct sum is 38
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).
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).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial