Menu
Community /Pin to ProfileBookmark
@NogDogFeb 26.2020 — #
I believe you can use
Or something like that. Just quickly hacking something together to see if it sparks any ideas for you. :)@NogDogFeb 27.2020 — #This runs...does it give the results you're looking for?
@NogDogFeb 27.2020 — #Probably have to add the date range to the sub-query on
All of which is making me die a little bit inside. ;)
Frankly, it might perform better under 2 separate queries anyway. :|
LEFT JOIN distorting SUM results
I have a table of invoices with costs associated.
I have a linked table with misc costs as well
relationship is 1 to many misc costs
When I do a SUM() and multiple rows exist on RIGHT table, the values in the LEFT table are multiplied by the number of rows found.
DB Fiddle:
Original query with incorrect results for LABOUR, PARTS, POLS and SUBLET:
“`
SELECT SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) AS SUBLET,
SUM(am.misc_sales_amt) AS MISC
FROM AdvisorSalesData ad
LEFT JOIN AdvisorMiscSalesData am
ON (ad.customer_id=am.customer_id AND ad.invoice_no=am.invoice_no)
WHERE ad.customer_id IN (3)
Adjusted query – with incorrect result for MISC (NULL):
“`
SELECT SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) AS SUBLET,
(SELECT SUM(misc_sales_amt) FROM AdvisorMiscSalesData WHERE customer_id IN (3) AND invoice_no=ad.invoice_no ) AS MISC
FROM AdvisorSalesData ad
WHERE ad.customer_id IN (3)
How can I adjust to get the correct results for all 5 columns in one single query?
Sign in
to post a comment10 Comments(s) ↴
0
@NogDogFeb 26.2020 — #>@php-bgrader#1615258 When I do a SUM() and multiple rows exist on RIGHT table, the values in the LEFT table are multiplied by the number of rows found.
I believe you can use
SUM(DISTINCT left_table.column_name)0
@NogDogFeb 26.2020 — #Hmm...so really both of the tables on that query actually represent separate many-to-one relationships to the invoice/customer pair? I'm sort of feeling like that may be throwing us off. I'm wondering if it might work better to include those tables...<i>
</i>select
cus.id as customer_id, -- guessing at table/column names
inv.id as invoice_id, -- ditto
SUM(distinct ad.labour_cost) AS LABOUR,
SUM(distinct ad.part_cost) AS PARTS,
SUM(distinct ad.pol_cost) AS POLS,
SUM(distinct ad.sublet_cost) AS SUBLET,
SUM(distinct am.misc_sales_amt) AS MISC
from customer cus
inner join invoice inv on inv.customer_id = cus.id
left join AdvisorSalesData ad on ad.customer_id = cus.id and ad.invoice_no = inv.id
left join AdvisorMiscSalesData am on am.customer_id = cus.id and am.invoice_no = inv.id
where customer.id in (3)
group by cus.id, inv.id
Or something like that. Just quickly hacking something together to see if it sparks any ideas for you. :)
0
@NogDogFeb 27.2020 — #This runs...does it give the results you're looking for?<i>
</i>SELECT
ad.customer_id,
SUM(ad.labour_cost) AS LABOUR,
SUM(ad.part_cost) AS PARTS,
SUM(ad.pol_cost) AS POLS,
SUM(ad.sublet_cost) AS SUBLET,
(
SELECT SUM(misc_sales_amt)
FROM AdvisorMiscSalesData
WHERE customer_id = ad.customer_id
) AS MISC
FROM AdvisorSalesData ad
WHERE ad.customer_id IN (3)
GROUP BY ad.customer_id
<i>
</i>customer_id LABOUR PARTS POLS SUBLET MISC
3 245.37 662.95 83.32 0 -252.46
0
@NogDogFeb 27.2020 — #Probably have to add the date range to the sub-query on AdvisorMiscSalesData0
@NogDogFeb 28.2020 — #Ugh...feels like we need some database re-design/re-normalization, perhaps? If that's not possible, then all I can think of is adding something like this to the where clause of the sub-query in my last example:<i>
</i>(
SELECT SUM(misc_sales_amt)
FROM AdvisorMiscSalesData
WHERE customer_id = ad.customer_id AND invoice_no IN (
SELECT invoice_no FROM AdvisorSalesData
WHERE ro_close BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW()
) AS invoices
) AS MISC
All of which is making me die a little bit inside. ;)
Frankly, it might perform better under 2 separate queries anyway. :|