Click here to Skip to main content
15,064,915 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
How to use query sum order and group by in sql?

This error code :
SUM(A.COLUMN3) ,SUM(B.COLUMN4) 


I want to if column 2 ---> 3 row ,sum (3 rows)
if column 2 ---> 4 row, sum (4 rows)


example:

Column 1	Column 2	Column 3	Column 4	SUM
A	            1	         1	        2	    1+5+3=9 AND 2+5+4=11 
A	            2	         5	        5	
A	            3	         3	        4	
B	            1	         2	        2	     2+1+3    AND  2+4+2             	B 	            1	         4	        2
B	            3	         3	        2

or

Column 1	Column 2	Column 3	Column 4	SUM
A	            1	         1	        2	    1+5+3+4=9 AND 2+5+4+5=16 
A	            2	         5	        5	
A	            3	         3	        4	
A               4            1          5
B	            1	         2	        2	     2+1+3+1   AND  2+2+2+6B             	B 	            2	         4	        2
B	            3	         3	        2	
B               4            1          6


What I have tried:

SELECT A.COLUMN1,A.COLUMN2,A.COLUMN3,B.COLUMN4, SUM(A.COLUMN3) ,SUM(B.COLUMN4) FROM

(select COLUMN1,COLUMN2,COLUMN3 from TABLE A WHERE COLUMN2 BETWEEN 1 AND 3
GROUP BY COLUMN1,COLUMN2,COLUMN3 ORDER BY COLUMN1,COLUMN2,COLUMN3) A
LEFT JOIN
(SELECT COLUMN1,COLUMN2,COLUMN4 FROM TABLE B WHERE COLUMN2 BETWEEN 1 AND 3
GROUP BY COLUMN1,COLUMN2,COLUMN4 ORDER BY COLUMN1,COLUMN2,COLUMN4)

ON A.COLUMN1=B.COLUMN1
AND A.COLUMN2=B.COLUMN2
Posted
Updated 9-Aug-21 20:18pm

1 solution

Start by not calling your tables "table a" and "table b" - if you include spaces in table names, then you need to use square brackets each time you use them.

Try adding the aggregate function to the SELECT of each GROUP BY subquery:
SQL
...
(SELECT Column1, Column2, Column3, SUM(Column3) AS Total FROM [Table A] 
 WHERE Column2 BETWEEN 1 AND 3
 GROUP BY Column1, Column2, Column3 
 ORDER BY Column1, Column2, Column3) A
...

You can then use the sum directly in the outer SELECT.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900