sum is not getting correctly while joining the tables

Question

Here i have 3 tables name A,B,C respectively and i want to join all the tables and fetch out the results

In order to get the desired output i wrote my code like this

SELECT  A.date as d_date,B.agent_name,    
    (SELECT SUM(B.profit) FROM B WHERE A.id = B.bill_id) AS total_profit,      
    SUM(C.total_price) AS t_price,SUM(C.total_dc) AS t_dc    
FROM A LEFT JOIN B ON A.id=B.bill_id    
       LEFT JOIN C ON C.data_id=B.id    
WHERE DATE(A.date) BETWEEN '{$start_date}' AND '{$end_date}' 
    AND A.customerid=406   
GROUP BY Date(A.date),A.customerid
ORDER BY A.id;

The problem is am getting Purchase value as the first value of the profit column from the table B.

i want my desired output to be like this

Name    Date          Purchase   t_price   t_dc
Ned     2019-07-26      210.60         80      40

but am getting like this

Name    Date          Purchase   t_price   t_dc
Ned     2019-07-26      15.60      80       40

here is the demo http://sqlfiddle.com/#!9/c85a910/3

See: [Why should I provide an MCRE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Strawberry, Sep 10 '19 at 11:00
i don't have much experience so that's y and i can tell what you didn't understood — user_777, Sep 10 '19 at 12:43
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Sep 11 '19 at 02:38
"getting total_profit value as the first value of the profit from the table B" is not clear. And why is that a problem? You don't describe or give an example of what result you want. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Sep 11 '19 at 02:42
Please in code questions give a [mre]--cut & paste & runnable code; example input (as tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. — philipxy, Sep 11 '19 at 02:45
It's good that you added desired & (alleged) actual output. Now please act on the rest of our comments. — philipxy, Sep 12 '19 at 14:09
Now please act on the rest of our comments, including putting the demo in your post as text & giving the rest of a [mre], including explaining how your output is a function of input & finding the subexpression that first gives what you don't expect & what your problem is & your version of MySQL, etc. How many times do you expect us to tell you? PS The query & example output in your post disagrees with your link. — philipxy, Sep 13 '19 at 06:07
Now that we can see a full example (that should be text in your post)--You don't declare any constraints. What is your justification for your expectation that an expression that is not an element of the group by list is single-valued per Date(A.date)-A.customerid pair & so can be used unaggregated in the select clause? Read about how group by works & what its requirements are. (Including in the online manual.) [Error related to only_full_group_by when executing a query in MySql](https://stackoverflow.com/q/34115174/3404097) — philipxy, Sep 13 '19 at 06:27

Ankit Bajpai · Answer 1 · 2019-09-13T07:01:14.213

1

The problem here is, Table C have 2 rows and both having data_id as 67159. So when you will join it with table B it will count the profit for bill_id 67159 twice. You have to use one condition to pick only 1 row. I have updated your query to -

SELECT  A.date as d_date,B.agent_name, SUM(B.profit) AS total_profit,      
    SUM(C.total_price) AS t_price,SUM(C.total_dc) AS t_dc    
FROM A LEFT JOIN B ON A.id=B.bill_id
                   AND A.customerid = B.user_id
       LEFT JOIN C ON C.data_id=B.id    
WHERE DATE(A.date) BETWEEN '2019-07-26' AND '2019-07-26' 
    AND A.customerid=406   
GROUP BY Date(A.date),B.agent_name
ORDER BY A.id;

This query is giving total_profit as 226.2.

edited Sep 13 '19 at 07:01

answered Sep 13 '19 at 05:35

Ankit Bajpai

13,128
4
25
40

No Problem. As I can see apart from `assigned_result_id` the whole row is same in table C. You must remove one of them. As soon as you will remove one of them, You will get your correct result. – Ankit Bajpai Sep 13 '19 at 10:32
Here can't remove the row because that 2 rows values are taking in `t_price` and `t_dc`.so how can we remove that – user_777 Sep 13 '19 at 10:43
In that case you only need to select 1 row of them. Now its your decision to pick the row with min assigned_result_id or max assigned_result_id. – Ankit Bajpai Sep 13 '19 at 10:45
@user_777 You can also write a subquery to remove whatever duplicates you decide you don't want, although the easiest way is still via min or max. But--presumably C is a join of some other tables & what you really want is to sum over one of those. – philipxy Sep 13 '19 at 21:43

sum is not getting correctly while joining the tables

1 Answers1