Richard Quinn Richard Quinn - 9 months ago 23
SQL Question

Two sql sums in one query with two where clauses

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

Answer

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