How to get only month & year in SQL QUERY (OPERA)-CodePudding

select ST.dateOfSale, ST.agreedPrice - C.purchasedPrice AS PROFIT
FROM SalesTransaction ST, Car C
WHERE ST.VIN = C.VIN
GROUP BY (ST.dateOfSale, (ST.agreedPrice - C.purchasedPrice))
ORDER BY ST.dateOfSale DESC ;

For each month in 2020, I need to display the total profit generated from car sales only

Please see thje table below:

Car(VIN, dateAcquired, yearBuilt, purchasedPrice, askingPrice)

SalesTransaction(VIN, custID, agentID, dateOfSale, agreedPrice)

CodePudding user response：

Good practice - use JOIN s to join multiple tables

SELECT to_char(ST.dateOfSale,'YYYY'),to_char(ST.dateOfSale, 'mm'),SUM( ST.agreedPrice - C.purchasedPrice) AS PROFIT
FROM SalesTransaction ST, Car C
WHERE ST.VIN = C.VIN
GROUP BY to_char(ST.dateOfSale,'YYYY'),to_char(ST.dateOfSale, 'mm');