Grouping By a To_Char

[guajaj]

I'm trying to show total profit per year of publication, but my pubdate's are all in the full date format.

I tried this, but I doesn't quite work:

SELECT TO_CHAR(pubdate, 'YYYY') "Year Published", sum(quantity) * (retail - cost) "Total Profit"
FROM books JOIN orderitems USING(isbn)
GROUP BY pubdate, (retail-cost);

I get a Profit for each book, not each year, there are duplicate years.

[frozenmist]

Hi,
If you want to group by year do
GROUP BY TO_CHAR(pubdate, 'YYYY')

Cheers

[chandu031]

I'm trying to show total profit per year of publication, but my pubdate's are all in the full date format.

I tried this, but I doesn't quite work:

SELECT TO_CHAR(pubdate, 'YYYY') "Year Published", sum(quantity) * (retail - cost) "Total Profit"
FROM books JOIN orderitems USING(isbn)
GROUP BY pubdate, (retail-cost);

I get a Profit for each book, not each year, there are duplicate years.

Hi,

Try this :

SELECT TO_CHAR(pubdate, 'YYYY') "Year Published", sum(quantity * (retail - cost)) "Total Profit"
FROM books JOIN orderitems USING(isbn)
GROUP BY TO_CHAR(pubdate, 'YYYY')
 

[frozenmist]

Hi,
If you want to group by year do
GROUP BY TO_CHAR(pubdate, 'YYYY')

Cheers

you have to change sum function as
sum(quantity *(retail-cost))

Cheers