asked
Group values
Hi! Can you consider only one value per ID for the Value Sell column? It turns out that the sale of 1,000.00 was received twice in 500.00 and in the query I did, of course, the value of 1,000.00 was repeated in each low. Could the received value be grouped by ID? ID Sales Received 1 1.000,00 500,00 1 1.000,00 500,00 2 400,00 200,00 2 400,00 200,00 Total 1.400,00 1.400,00
Can you provide sample data that matches your database objects, and show the query that you have been using, and the actual & expected output together?
If there is no possibility that there is yet another sale for the ***same amount*** and ***same ID*** in addition to the split sales records then this will work: select ID, sum(distinct Sales) Sales, sum(Received) Received from YourTable group by ID -- with rollup; If you uncomment the rollup part, it will show the grand totals in the last record. If there is a need to calculate just the grand totals for all IDs and all sales assuming that the condition I outlined in the beginning of this answer holds then this will work: select sum(Sales) TotalSales, sum(Received) TotalReceived from ( select ID, sum(distinct Sales) Sales, sum(Received) Received from YourTable group by id ) t; If the possibility exists that there are multiple sales for the same amount and same ID and each may or may not be split into multiple received then the solution above will not work as is. In this case, more details about the source data are needed in order to come up with the correct solution. Hope this helps. Oleg
Hello, Thank you for your interest in helping me. In the actual situation, I applied the first SQL and returned two rows because the received values are not the same for the invoice. How to ignore this and make the sum? I was confused because of the join. Thank you! SELECT A.IDLAN ID , B.CODCFO AS CUSTOMER , SUM(DISTINCT B.VALORORIGINAL) SALES , A.NUMCONTABILBX NUMBER , SUM(A.VALORORIGINAL) RECEIVED FROM FLANBAIXA AS A INNER JOIN FLAN AS B ON A.IDLAN = B.IDLAN AND A.CODCOLIGADA = B.CODCOLIGADA WHERE A.NUMCONTABILBX = '008198/01' GROUP BY A.IDLAN , B.CODCFO , B.VALORORIGINAL , A.NUMCONTABILBX , A.VALORORIGINAL
@Gelain Because you are including the aggregates on B.VALORORIGINAL (sum of distinct values) and A.VALORORIGINAL (sum of values), you must not include these columns in the group by list. Once you remove them, the select should work. Here is how it should look: SELECT A.IDLAN ID, B.CODCFO AS CUSTOMER, SUM(DISTINCT B.VALORORIGINAL) SALES, A.NUMCONTABILBX NUMBER, SUM(A.VALORORIGINAL) RECEIVED FROM FLANBAIXA AS A INNER JOIN FLAN AS B ON A.IDLAN = B.IDLAN AND A.CODCOLIGADA = B.CODCOLIGADA WHERE A.NUMCONTABILBX = '008198/01' GROUP BY A.IDLAN, B.CODCFO, A.NUMCONTABILBX;
18 People are following this question.
