cancel
Showing results for 
Search instead for 
Did you mean: 

SQL for revenue by top 5 states

Highlighted
Established Member

SQL for revenue by top 5 states

Hi

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

Miles

 

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
1 REPLY
Magento Team

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.

 

Thanks,

Akash Agrawal

Senior Analyst, Magento Business Intelligence