Create a summary view

I need to create view that displays revenue like below. (amusement park database)

I have 3 tables:

x_transaction (transaction_id, rides_id, amount, ...)
x_rides (rides_id, rides_category_id,...)

x_rides_category (rides_category_id, name, ...)


And I need to create a view that displays total revenue for each rides category

category    revenue
------------   -----------
sport          34000
water         27000
adrenaline 24300
adult          null

BTW sorry for my bad English.

2 answers

  • answered 2019-12-15 02:32 Bob Jarvis - Reinstate Monica

    The following should produce what you're looking for:

    CREATE VIEW RIDE_CATEGORY_REVENUE_VIEW AS
      SELECT c.NAME AS CATEGORY, SUM(t.AMOUNT) AS REVENUE
      FROM X_RIDES_CATEGORY c
      INNER JOIN X_RIDES r
        ON r.RIDES_CATEGORY_ID = c.RIDES_CATEGORY_ID
      INNER JOIN X_TRANSACTION t
        ON t.RIDES_ID = r.RIDES_ID
    

    I question whether the name of the second field on X_TRANSACTION is RIDE_ID or RIDES_ID. The latter matches the corresponding field on X_RIDES, but I've used the first name as that's what you showed in your summary of the X_RIDES table.

  • answered 2019-12-15 03:32 Tejash

    I think you can use outer joins and group by as following:

    Create view your_view_name as
    Select cat.name as category, sum(amount) as revenue
      From x_rides_category cat
      Left join x_rides r on cat.rides_category_id = r.rides_category_id
      Left join x_transaction t on r.rides_id = t.rides_id
    Group by cat.name
    

    Cheers!!