Search code examples
mysqlsqlrevenue

Calculate total revenue made from each item broken down by country and day

I have a table which consists:

id     country    Date     item_name    price

ae3u2  USA     27/12/2018  budget      1.99
bf5d8  India   31/12/2018  everything  34.99
dc8a4  USA     22/01/2019  cars        25.99

and it goes on. I have to calculate:

  1. total revenue
  2. total revenue made from each item broken down by country and day.

I am confused how to calculate it as i don't have "quantity" and i am also not much experienced.

Solution

  • You can use with rollup, but you will get additional rows:

    select item_name, country, date, sum(price)
from t
group by item_name, country, date with rollup

    You can filter out the intermediate rows as:

    having grouping(item_name, country, date) in (0, 7)