Making a query more efficient

Databases
1

I have posted this question before but it got convoluted with too many details, off-shoots, etc. I thought it best to start again.

I have a big query (please see below). It works, but I thought that there may be a better way of writing it.

For example, is it possible to use a LOOP function? How would that query look?

Many thanks…

SELECT
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_of_disposition,1,4) = ($year - 10) AND prints.sale_price > 0) as year1,
(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN(prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_of_disposition,1,4) = ($year - 9) AND prints.sale_price > 0) as year2,
.......


(SELECT SUM(CASE WHEN prints.currency = 'US Dollars'
                THEN prints.sale_price
                WHEN prints.currency = 'Euros'
                THEN (prints.sale_price * $euro)
                ELSE NULL END)   FROM prints WHERE  SUBSTR(prints.date_of_disposition,1,4) = ($year + 1) AND prints.sale_price > 0) as year12



FROM prints 
GROUP BY year1, year2, year3, year4 ";
2

my advice is that you should not write such a complicated mess

set up a table of currencies and a table of years and the query becomes trivially simple

but i’ve said it before and i don’t think you’re receptive…

:cool: