Using multiple JOINS. SUM() producing wrong value
If you run the query without a group by, you'll see that some payments have multiple rows. That's because you're also joining on order items. The result set will contain a row for each combination of orderitem and payment.
One solution would be to change the sum to:
, <earlier columns>
, ( select SUM(payments.amount)
from payments
where payments.orderID = orders.id
) AS totalPayments
, <later columns>
This would ensure the payments with multiple orderitems are not summed multiple times.
Getting correct sums with multiple joins
The subquery idea untested
SELECT PNR, isnull(CR.CREDITS,0) AS CREDITS,
...
FROM DATABASE.PERSON P
LEFT OUTER JOIN (
SELECT CRQ.PERSON_ID, SUM(C.CREDIT) AS CREDITS
-- I'm not really sure how to associate a person with the credits
-- but hopefully you'll get the idea here.
FROM DATABASE.PICKED_COURSE PCO
INNER JOIN DATABASE.COURSE_REG_QUAL CRQ on CRQ.COURSE_ID=C.COURSE_ID
GROUP BY CRQ.PERSON_ID
) CR on CR.PERSON_ID = P.PERSON_ID
LEFT JOIN DATABASE.PERSON_ADDRESS PA ON P.PERSON_ID=PA.PERSON_ID
LEFT JOIN DATABASE.DIPLOMA DU ON P.PERSON_ID=DU.PERSON_ID
...
Problems with multiple joins and a sum
You would typically pre-aggregate in subqueries before joining, like so:
SELECT p.*
COALESCE(v.voteCount, 0) as voteCount,
COALESCE(v.userVote, 0) as userVote,
COALESCE(c.numComments, 0) as numComments
FROM post p
LEFT JOIN (
SELECT postID,
SUM(vote) as voteCount,
SUM(vote) FILTER (WHERE username = :username) userVote
FROM votes
GROUP BY postID
) v ON v.postID = p.postID
LEFT JOIN (
SELECT postID, count(*) numComments
FROM comments
WHERE parentID IS NULL
GROUP BY postID
) c ON c.postID = p.postID
ORDER BY voteCount DESC
N1QL Multiple join and sum query producing wrong output
JOINS can expand the original rows. During first Join left most document is expanded. You are using first document field as second join condition that can produce multiple documents same condition. This how semantics works. You need to adjust your join condition based on the needs.
The second JOIN uses one document from the LEFT most. SUM over DISTINCT may not work because different documents same value (warm_coffee, may under count) is counted once. If I am right you are looking second join to use unique document from LEFT most coffee.
May be you are looking some thing like this
SELECT c.coffeeId,
MAX(c.ccNumber) AS `ccNumber`,
SUM(warm_coffee.`number`) AS `wcNumber`,
FROM ( SELECT META(coffee).id AS coffeeId,
SUM(cold_coffee.`number`) AS `ccNumber`
FROM coffee_bucket coffee
LEFT JOIN coffee_bucket cold_coffee
ON META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
WHERE coffee.type='coffee'
GROUP BY META(coffee).id
) AS c
LEFT JOIN coffee_bucket warm_coffee
ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
GROUP BY c.coffeeId;
3 level join
SELECT d.coffeeId,
MAX(c.ccNumber) AS `ccNumber`,
MAX(c.wcNumber) AS `wcNumber`,
SUM(ch.`number`) AS `chNumber`
FROM ( SELECT c.coffeeId,
MAX(c.ccNumber) AS `ccNumber`,
SUM(warm_coffee.`number`) AS `wcNumber`,
FROM ( SELECT META(coffee).id AS coffeeId,
SUM(cold_coffee.`number`) AS `ccNumber`
FROM coffee_bucket coffee
LEFT JOIN coffee_bucket cold_coffee
ON META(coffee).id = cold_coffee.coffeeId AND cold_coffee.type='cold_coffee'
WHERE coffee.type='coffee'
GROUP BY META(coffee).id
) AS c
LEFT JOIN coffee_bucket warm_coffee
ON c.coffeeId = warm_coffee.coffeeId AND warm_coffee.type='warm_coffee'
GROUP BY c.coffeeId) AS d
LEFT JOIN coffee_bucket ch
ON d.coffeeId = ch.coffeeId AND ch.type='chaoc_coffee'
GROUP BY d.coffeeId
;
Incorrect sum when I join a second table
Group records per date using following
SELECT ReportSummary.ReportDate, SUM(ReportSummary.SumHeadCount) AS SumHeadCount, SUM(ProductionSummary.SumQuantity) AS SumQuantity
FROM
(
SELECT Report.ReportDate, SUM(Report.HeadCount) AS SumHeadCount
FROM Report
GROUP BY Report.ReportDate
) AS ReportSummary
INNER JOIN
(
SELECT Report.ReportDate, Sum(Production.Quantity) AS SumQuantity
FROM Production
INNER JOIN Report
ON Report.ReportID = Production.ReportID
GROUP BY Report.ReportDate
) AS ProductionSummary
ON ReportSummary.ReportDate = ProductionSummary.ReportDate
GROUP BY ReportSummary.ReportDate
ORDER BY ReportSummary.ReportDate
MySQL: SUM() with JOIN returns incorrect values
Try this query:
SELECT bl.user_id, SUM( ph.amount ) PAIDOUT
FROM (
SELECT distinct blocks.user_id
FROM blocks
WHERE confirms > 520
) bl
LEFT JOIN payout_history ph
ON bl.user_id = ph.user_id
GROUP BY ph.user_id
;
SQLFiddle --> http://sqlfiddle.com/#!2/7b988/48
--- EDIT --- an explanation how the query works (or rather why your query doesn't work) ----
Looking at expected results it seems that the query should calculate a sum of amount column for each user_id, but only for those user_id, that are also in the blocks table, and have a blocks.confirms value grather than 520.
A simple join (also left outer join) cannot work in this case, because the blocks table can contain many records for the same user_id, for example a query that returns rows for only user_id=110 gives the following results:
SELECT *
FROM blocks
WHERE confirms > 520
AND user_id = 110;
+ ------- + ------------ + ----------- + ------------- +
| id | user_id | reward | confirms |
+ ------- + ------------ + ----------- + ------------- +
| 0 | 110 | 20.89832115 | 521 |
| 65174 | 110 | 3.80357075 | 698 |
| 65204 | 110 | 4.41933060 | 668 |
| 65218 | 110 | 4.69059801 | 654 |
| 65219 | 110 | 4.70222521 | 653 |
| 65230 | 110 | 4.82805490 | 642 |
| 65265 | 110 | 5.25058079 | 607 |
| 65316 | 110 | 6.17262650 | 556 |
+ ------- + ------------ + ----------- + ------------- +
The straigh join (and LEFT/RIGHT outer join) works in this way, that takes each record from the first joinded table, and pair this record (combine it) with all rows from the other joinded table thet meet the join condition.
In our case the left join produces a below resultset:
SELECT *
FROM blocks
LEFT JOIN payout_history
ON blocks.user_id = payout_history.user_id
WHERE confirms > 520
AND blocks.user_id = 110;
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +
| id | user_id | reward | confirms | id | user_id | amount |
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +
| 0 | 110 | 20.89832115 | 521 | 1 | 110 | 20.898319 |
| 65174 | 110 | 3.80357075 | 698 | 1 | 110 | 20.898319 |
| 65204 | 110 | 4.41933060 | 668 | 1 | 110 | 20.898319 |
| 65218 | 110 | 4.69059801 | 654 | 1 | 110 | 20.898319 |
| 65219 | 110 | 4.70222521 | 653 | 1 | 110 | 20.898319 |
| 65230 | 110 | 4.82805490 | 642 | 1 | 110 | 20.898319 |
| 65265 | 110 | 5.25058079 | 607 | 1 | 110 | 20.898319 |
| 65316 | 110 | 6.17262650 | 556 | 1 | 110 | 20.898319 |
+ ------- + ------- + ----------- + -------- + --- + ------- + ----------- +
and now if we add SUM( amount ) .... GROUP BY user_id, MySql will calucate a sum of all amount values from the above resultset ( 8 rows * 20.898 = ~ 167.184 )
SELECT blocks.user_id, sum( amount)
FROM blocks
LEFT JOIN payout_history
ON blocks.user_id = payout_history.user_id
WHERE confirms > 520
AND blocks.user_id = 110
GROUP BY blocks.user_id;
+ ------------ + ----------------- +
| user_id | sum( amount) |
+ ------------ + ----------------- +
| 110 | 167.186554 |
+ ------------ + ----------------- +
As you see in this case the join doesn't give us desired results - we need something named a semi join - below are different variants of semi joins, give them a try:
SELECT bl.user_id, SUM( ph.amount ) PAIDOUT
FROM (
SELECT distinct blocks.user_id
FROM blocks
WHERE confirms > 520
) bl
LEFT JOIN payout_history ph
ON bl.user_id = ph.user_id
GROUP BY ph.user_id
;
SELECT ph.user_id, SUM( ph.amount ) PAIDOUT
FROM payout_history ph
WHERE ph.user_id IN (
SELECT user_id FROM blocks
WHERE confirms > 520
)
GROUP BY ph.user_id
;
SELECT ph.user_id, SUM( ph.amount ) PAIDOUT
FROM payout_history ph
WHERE EXISTS (
SELECT 1 FROM blocks bl
WHERE bl.user_id = ph.user_id
AND bl.confirms > 520
)
GROUP BY ph.user_id
;
Mysql sum query returning incorrect result when using multiple joins
Use the totals directly because your joins are probably creating more rows in combination that you want.
Try the following:
SELECT id, MAX(Total) as FinalTotal ,MAX(Payment) as FinalPayment
FROM si_invoices a
left join
(select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b
on a.id = b.invoice_id
left join
(select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c
on c.ac_inv_id = a.id
group by id
or if id is unique:
SELECT *
FROM si_invoices a
left join
(select invoice_id, sum(total) as Total from si_invoice_items group by invoice_id) b
on a.id = b.invoice_id
left join
(select ac_inv_id, sum(payment) as Payment from si_payment group by ac_inv_id) c
on c.ac_inv_id = a.id
SQL multiple tables join problem with SUM Results Multiples times Higher Value
Records count are differing each tables, that's why value increasing due to duplication of records, to avoid this take sum before joining a tables,. It will give better results and also your expected result also wrong for the sample data.
Suggestion: To better result you can replace RIGHT OUTER JOIN with LEFT OUTER JOIN.
Here is the code:
--------Table Creation Part-----
CREATE TABLE #tblretailer
(
ID INT,
[NAME] VARCHAR(50),
PLACE VARCHAR(25),
PHONE VARCHAR(10),
)
GO
CREATE TABLE #tbldraw
(
ID INT,
RetailerID INT,
DrawNumber INT,
[Count] INT,
CreatedDate DATE
)
GO
CREATE TABLE #Tbl_ABC
(
ID INT,
RetailerID INT,
ANum INT,
BNum INT,
CNum INT,
ACount INT,
BCount INT,
CCount INT,
CreatedDate DATE
)
GO
CREATE TABLE #Tbl_ABACBC
(
ID INT,
RetailerID INT,
ANum INT,
BNum INT,
CNum INT,
ABCount INT,
ACCount INT,
BCCount INT,
CreatedDate DATE
)
GO
/*
DROP TABLE #tblretailer, #tbldraw, #Tbl_ABC, #Tbl_ABACBC
GO
*/
-----Value Insert-------
INSERT INTO #tblretailer
SELECT 3, 'Zen Commmunications','Navi mumbai','3456728102'
UNION ALL
SELECT 4, 'ASpire Systems','New Delhi','4324535453'
UNION ALL
SELECT 5, 'Zen Commmunications','New Delhi','4324535454'
GO
INSERT INTO #tbldraw
SELECT 2368, 4, 123, 10, '2020-06-01'
UNION ALL
SELECT 2370, 4, 777, 15, '2020-06-01'
UNION ALL
SELECT 2371, 3, 153, 100, '2020-06-01'
UNION ALL
SELECT 2372, 3, 758, 50, '2020-06-01'
UNION ALL
SELECT 2374, 3, 777, 10, '2020-06-01'
GO
INSERT INTO #Tbl_ABC
SELECT 21, 4, 1, 2, 3, 10, 5, 15, '2020-06-01'
UNION ALL
SELECT 22, 4, 1, 2, 3, 10, 20, 15, '2020-06-01'
UNION ALL
SELECT 23, 3, 1, 2, 3, 10, 25, 0, '2020-06-01'
UNION ALL
SELECT 24, 3, 1, 2, 3, 80, 0, 0, '2020-06-01'
UNION ALL
SELECT 25, 3, 1, 2, 3, 10, 5, 20, '2020-06-01'
GO
INSERT INTO #Tbl_ABACBC
SELECT 9, 4, 12, 20, 30, 10, 10, 10, '2020-06-01'
UNION ALL
SELECT 10, 4, 12, 20, 30, 15, 15, 15, '2020-06-01'
UNION ALL
SELECT 11, 3, 12, 20, 30, 15, 20, 0, '2020-06-01'
UNION ALL
SELECT 12, 3, 12, 20, 30, 0, 10, 20, '2020-06-01'
GO
----Query Part-----
DECLARE @Date DATE = '2020-06-01'
SELECT
RETAILER.id,
NAME AS retailername,
atotal,
Btotal,
Ctotal,
strtotal,
-- SUM(ISNULL(boxcount, 0)) AS boxtotal,
abtotal,
actotal,
bctotal
FROM
#tblretailer AS RETAILER
RIGHT JOIN
(
SELECT
ABC.retailerid
, SUM(ISNULL(acount, 0)) AS atotal
, SUM(ISNULL(bcount, 0)) AS Btotal
, SUM(ISNULL(ccount, 0)) AS Ctotal
FROM
#tbl_abc AS ABC
WHERE
ISNULL(ABC.createddate, CAST(GETDATE() AS DATE)) = @Date
GROUP BY
ABC.retailerid
) AS ABC ON ABC.retailerid = RETAILER.id
RIGHT JOIN
(
SELECT
DRAW.retailerid
--, SUM(ISNULL(boxcount, 0)) AS boxtotal
, SUM(ISNULL(count, 0)) AS strtotal
FROM
#tbldraw AS DRAW
WHERE
ISNULL(DRAW.createddate, CAST(GETDATE() AS DATE)) = @Date
GROUP BY
DRAW.retailerid
) AS DR ON DR.retailerid = RETAILER.id
RIGHT JOIN
(
SELECT
ABACBC.retailerid
, SUM(ISNULL(abcount, 0)) AS abtotal
, SUM(ISNULL(account, 0)) AS actotal
, SUM(ISNULL(bccount, 0)) AS bctotal
FROM
#tbl_abacbc AS ABACBC
WHERE
ISNULL(ABACBC.createddate, CAST(GETDATE() AS DATE)) = @Date
GROUP BY
ABACBC.retailerid
) AS ABACBC ON ABACBC.retailerid = RETAILER.id
--GROUP BY
-- RETAILER.id, RETAILER.NAME , RETAILER.straightrate, RETAILER.boxrate, RETAILER.abcrate, RETAILER.abrate
Result :
id retailername atotal Btotal Ctotal strtotal abtotal actotal bctotal
1 3 Zen Commmunications 100 30 20 160 15 30 20
2 4 ASpire Systems 20 25 30 25 25 25 25
Related Topics
Stratified Random Sampling with Bigquery
SQL Join Table Naming Convention
How to Import a SQL Data File into SQL Server
Executing a Stored Procedure Inside Begin/End Transaction
What Are Indexes and How to Use Them to Optimize Queries in My Database
A Good Reference for Oracle Pl/Sql
How to Clear Oracle Execution Plan Cache for Benchmarking
How to Analyse a SQLite Query Execution
Why Should I Capitalize My SQL Keywords
How to Do a Simple 'Find and Replace" in Mssql
Calling a Stored Procedure in Oracle with in and Out Parameters
Exporting Blob from MySQL Database to File with Only SQL
Postgresql Calculate Difference Between Rows