0

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.

ankur singh
  • 72
  • 1
  • 7
  • 1
    I guess price is a synonym for revenue.. – P.Salmon Nov 18 '20 at 12:52
  • I think so but i am not sure as revenue is generally calculated using price and quantity thats why everytime my mind goes towards quantity. – ankur singh Nov 18 '20 at 14:08
  • 1
    Can't help you with that..I'm not getting a clue from table name or table contents..if you have doubts go back to whoever gave you this assignment. – P.Salmon Nov 18 '20 at 14:11

1 Answers1

0

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)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I got the required answer for "Total revenue made from each item broken down by country and day." but i didn't get the use of "having" clause here, can you please explain it ? – ankur singh Nov 18 '20 at 14:16
  • I am also gettting "Null" values using this query but in the original data there are no null values. – ankur singh Nov 18 '20 at 14:30
  • 1
    @ankursingh . . . Those "null" values are on rows that are aggregated. – Gordon Linoff Nov 18 '20 at 16:26
  • Also Can you please explain the use of "having" clause here ? As without using it also answer is the same. Thanks. – ankur singh Nov 18 '20 at 18:31
  • 1
    @ankursingh . . . The `having()` clause should be filtering out certain intermediate results. The last version does *not* work -- and I don't know why, so I removed it. Here is a db<>fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ab17577d31ae8574cb82e6642fef04f3. – Gordon Linoff Nov 18 '20 at 23:29