Showing results for 
Search instead for 
Did you mean: 

SQL for revenue by top 5 states

SQL for revenue by top 5 states


I am trying to relicate the "Revenue by top 5 states" chart in the Executive overview dashboard using SQL but I can't seem to be able to do this. Can anyone help please?


Many thanks



SELECT "Billing address city", SUM("base_grand_total") as "Base grand total", extract(year from "created_at (use)") as "Year", extract(month from "created_at (use)") as "Month" FROM "sales_order" WHERE "created_at (use)" >= '2016-01-01 00:00:00' GROUP BY "Billing address city","Year","Month" ORDER BY "Billing address city" ASC LIMIT 100

Re: SQL for revenue by top 5 states

Hi Miles - 


Could you share where you're attempting to replicate the SQL query in?


The columns `Billing address city` and `created_at (use)` are only available on your Magento BI Data Warehouse and not in the database on your end, so these columns will not be recognized by your SQL client.



Akash Agrawal

Senior Analyst, Magento Business Intelligence