SQL - How can I do sum of transactions for certain services (that have different pricing) for each month of the year?

Question

So I had this:

SELECT 
    MONTH(trans_date) AS Month_Booking, 
    count(transact_no) AS NumTransactions, 
    (price * count(transact_no)) AS TotalRevenue
FROM transaction t
JOIN service s ON t.service_no = s.service_no
WHERE YEAR(trans_date) = 2017
GROUP BY MONTH(trans_date)
ORDER BY MONTH(trans_date) asc;

Which returned this result, which is wrong because it doesn't change the price of each service depending on the transaction so the total revenue is wrong here. Result of Query Above

Then I tried this, which is partly complete since it gives me the revenue of one month - which is correct however since it adding different services depending on each transaction.

SELECT 
    sum(TotalRevenue) AS March_Revenue
FROM 
(
    SELECT 
        s.service_no, 
        description, 
        count(transact_no) AS NumTransactions, 
        (price * count(transact_no)) AS TotalRevenue, 
        trans_date
FROM service s
JOIN transaction t ON t.service_no = s.service_no
WHERE MONTH(trans_date) = 3 
AND YEAR(trans_date) = 2017
GROUP BY service_no
ORDER BY service_no asc) March;

Result of Query Above

What I would like, is to have a query that can somehow obtain total revenue for each month, without having to do it individually like this.

Any particular way to do so?

Thanks in advance.

Answer 1

Might be missing something, but I think its just

SELECT MONTH(trans_date) AS Month_Booking, 
    count(transact_no) AS NumTransactions, 
    sum(price) AS TotalRevenue
FROM transaction t
JOIN service s
ON t.service_no = s.service_no
WHERE YEAR(trans_date) = 2017
GROUP BY MONTH(trans_date)
ORDER BY MONTH(trans_date) asc;

Answer 2

This is not possible unless you have the historical price somewhere. If there is a price TRANSACTION_PRICE in the TRANSACTION table is is quite easy:

SUM(transaction_price)

If there is not you are lost. The data does not exist anymore in the database unless you have some secret auditing tables from which you can work out the historical price.

Answer 3

Shouldn't this work? Assuming service_no and price are 1:1

SELECT Month_Booking,
       SUM(cnt_service_no * price) AS Total_Revenue
  FROM (SELECT MONTH(trans_date) AS Month_Booking,
               COUNT(s.service_no) AS cnt_service,
               s.price
          FROM transaction t
         INNER
          JOIN service s
            ON t.service_no = s.service_no
         GROUP
            BY MONTH(trans_date),
               s.service_no,
               s.price
        )
 GROUP 
    BY Month_Booking;