CODEWITHSUNDEEP
sqloracle-databasesum
Asanka
Asanka

Reputation: 493

Joining multiple sum sql statements

How can I join multiple sql statements. I need to display in one view respectively 2004,2008,2009 and so on 

SELECT SUM(ACQUISITIONPRICE) AS YEAR_2007
FROM TRANS
WHERE DATEACQUIRED LIKE '%07';

SELECT SUM(ACQUISITIONPRICE) AS YEAR_2008
FROM TRANS
WHERE DATEACQUIRED LIKE '%08';

SELECT SUM(ACQUISITIONPRICE) AS YEAR_2009
FROM TRANS
WHERE DATEACQUIRED LIKE '%09';

Upvotes: 0

Views: 102

Answers (3)

Nathan
Nathan

Reputation: 10784

Just use GROUP BY like so:

SELECT SUM(ACQUISITIONPRICE) AS TOTALPRICE
, EXTRACT(year from DATEACQUIRED) AS YEAR
FROM TRANS
GROUP BY EXTRACT(year from DATEACQUIRED)
ORDER BY YEAR

If you really want to "join" them all in one row, with specific column names, you can do the following (though this approach is less flexible, since the query will have to be manually modified every year). Also note that although the question specifically mentioned "joining" the results, the join as shown below is completely unnecessary. Each column could simply be part of the select clause without any join. (here is the SqlFiddle)

SELECT * FROM
(SELECT SUM(ACQUISITIONPRICE) AS YEAR_2007
FROM TRANS
WHERE EXTRACT(year from DATEACQUIRED) = 2007) a
JOIN
(SELECT SUM(ACQUISITIONPRICE) AS YEAR_2008
FROM TRANS
WHERE EXTRACT(year from DATEACQUIRED) = 2008) b ON 1=1
JOIN
(SELECT SUM(ACQUISITIONPRICE) AS YEAR_2009
FROM TRANS
WHERE EXTRACT(year from DATEACQUIRED) = 2009) c ON 1=1

If you want the results in a single row, a preferable approach is to use a sub-select with dual as shown in this fiddle:

SELECT 
  (SELECT SUM(ACQUISITIONPRICE) FROM TRANS WHERE EXTRACT(year from DATEACQUIRED) = 2007) AS YEAR_2007
, (SELECT SUM(ACQUISITIONPRICE) FROM TRANS WHERE EXTRACT(year from DATEACQUIRED) = 2008) AS YEAR_2008
, (SELECT SUM(ACQUISITIONPRICE) FROM TRANS WHERE EXTRACT(year from DATEACQUIRED) = 2009) AS YEAR_2009
FROM dual

When it comes to performance, the only real way to be sure is of course to test it in your particular scenario, but the SUM (CASE... approach shown by @Indra-Prakash-Tiwari may perform better in most cases (if oracle behaves anything like sql server)

Upvotes: 1

A_Sk
A_Sk

Reputation: 4630

Try Use To_Char

Fiddle demo

select TO_CHAR(DATEACQUIRED,'yy') AS Years_Char,Sum(ACQUISITIONPRICE)
FROM trans GROUP BY TO_CHAR(DATEACQUIRED,'yy')

Upvotes: 0

Indra Prakash Tiwari
Indra Prakash Tiwari

Reputation: 1057

check this sql fiddle

I have handled your requirement with case statement which seems to be very easy. 

SELECT SUM(ACQUISITIONPRICE) AS TOTALPRICE
, EXTRACT(year from DATEACQUIRED) AS YEAR
FROM TRANS
GROUP BY EXTRACT(year from DATEACQUIRED)
ORDER BY YEAR;


SELECT Sum(CASE 
             WHEN Extract(year FROM dateacquired) = 2007 THEN acquisitionprice 
             ELSE 0 
           END) AS YEAR_2007, 
       Sum(CASE 
             WHEN Extract(year FROM dateacquired) = 2008 THEN acquisitionprice 
             ELSE 0 
           END) AS YEAR_2008, 
       Sum(CASE 
             WHEN Extract(year FROM dateacquired) = 2009 THEN acquisitionprice 
             ELSE 0 
           END) AS YEAR_2009 
FROM   trans

Upvotes: 1

Related Questions