I need the yearly total of non-insurance payments for the years of 2010 and 2011.
Although this is a relatively easy query, it is a good demonstration of the aggregate SUM function, the handy YEAR function, and the GROUP BY clause. Note that the requirement that only non-insurance payments be included means that we have to JOIN our way all the way from the credit entry to the payors table, where the payortype value is stored. In this case we can make the query more compact by taking advantage of “natural” joins that are already present among the four tables in the query. Natural joins use existing primary and foreign keys so we don’t have to specify the ON table.column = table.column syntax you usually would include in such queries. It doesn’t hurt to include it, but in this case there are natural relationships that imply the ON specifications.
SELECT YEAR(trandate) AS "yr", SUM (amount) AS "Payments"
FROM sos.journal a JOIN sos.jcredits b JOIN sos.ptpayors c JOIN sos.payors d
WHERE trantype ='P'
AND d.payortype <> 'I'
AND trandate BETWEEN '2010-01-01' and '2011-12-31'
GROUP BY "yr"
ORDER BY "yr"
