Two sql sums in one query with two where clauses

Question

Hi i want to join two sql statements into one so data will be displayed in one query.

The below sql queries work fine:

SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalSponsor
FROM Fees WHERE(Sponsor = 'True')

&

SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalLoan
FROM Fees WHERE(StudentLoan = 'True')

Basically i want to join them so the output will be like:

TotalSponsor   TotalLoan
10000          5000

Any help would be appreciated.

Thanks

score 1 · Accepted Answer · answered Apr 27 '16 at 20:00

You can combine the two rows with a CROSS JOIN. Those are usually recommended against because if you cross join two tables with M and N rows each, you get MxN rows in the result (which can be a lot), but in this case you know for a fact that M and N are both 1, so MxN is also 1.

SELECT *
FROM (
    SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalSponsor
    FROM Fees WHERE(Sponsor = 'True')
) AS x
CROSS JOIN (
    SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalLoan
    FROM Fees WHERE(StudentLoan = 'True')
) AS y

score 1 · Answer 2 · answered Apr 27 '16 at 20:03

One option, is conditional aggregation....

SELECT SUM(IF(f.Sponsor    ='True',CAST(CAST(Amount AS float) AS INT),0)) AS TotalSponsor
     , SUM(IF(f.StudentLoan='True',CAST(CAST(Amount AS float) AS INT),0)) AS TotalLoan
  FROM Fees f
 WHERE f.Sponsor = 'True'
    OR f.StudentLoan = 'True'

The IF() function evaluates the first argument as a boolean, if it evaluates to TRUE, it returns the second argument, else it returns the third argument.

You could use a CASE expression in place of IF for equivalent behavior...

 SUM(CASE WHEN  somecondition  THEN  somevalue  ELSE 0 END)

Or, you could use sbqueries in the SELECT list of an outer query, something like this:

SELECT ( SELECT SUM(CAST(CAST(Amount AS float) AS INT))
           FROM Fees
          WHERE(Sponsor = 'True') 
       ) AS TotalSponsor
     , ( SELECT SUM(CAST(CAST(Amount AS float) AS INT))
           FROM Fees
           WHERE(StudentLoan = 'True')
       ) AS TotalLoan

Or, you could use the queries as inline views

SELECT s.TotalSponsor
     , l.TotalLoan
  FROM ( SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalSponsor
           FROM Fees WHERE(Sponsor = 'True') 
       ) s
 CROSS
  JOIN  ( SELECT SUM(CAST(CAST(Amount AS float) AS INT)) AS TotalLoan
            FROM Fees WHERE(StudentLoan = 'True')
       ) l

Two sql sums in one query with two where clauses

2 Answers2