sales_by_store
| Documentation | Sakila |
| Name | sales_by_store |
| Module | Views |
The sales_by_store view provides a list of total sales, broken down by store.
The view returns the store location, manager name, and total sales.
The sales_by_store view incorporates data from the city, country, payment, rental, inventory, store, address, and staff tables.
Columns
| Key | Name | Data type | Null | Attributes | References | Description | ||
|---|---|---|---|---|---|---|---|---|
| 1 | store | varchar(101) | Information about store - city and country. | |||||
| Information about store - city and country. |
||||||||
| 2 | manager | varchar(91) | The staff member full name. | |||||
| The staff member full name. |
||||||||
| 3 | total_sales | decimal(27, 2) | The total sale. | |||||
| The total sale. |
||||||||
Script
select concat(`c`.`city`,',',`cy`.`country`) AS `store`,concat(`m`.`first_name`,' ',`m`.`last_name`) AS `manager`,sum(`p`.`amount`) AS `total_sales` from (((((((`sakila`.`payment` `p` join `sakila`.`rental` `r` on((`p`.`rental_id` = `r`.`rental_id`))) join `sakila`.`inventory` `i` on((`r`.`inventory_id` = `i`.`inventory_id`))) join `sakila`.`store` `s` on((`i`.`store_id` = `s`.`store_id`))) join `sakila`.`address` `a` on((`s`.`address_id` = `a`.`address_id`))) join `sakila`.`city` `c` on((`a`.`city_id` = `c`.`city_id`))) join `sakila`.`country` `cy` on((`c`.`country_id` = `cy`.`country_id`))) join `sakila`.`staff` `m` on((`s`.`manager_staff_id` = `m`.`staff_id`))) group by `s`.`store_id` order by `cy`.`country`,`c`.`city` |
Exported: 2018-10-12 17:43, Last imported: 2018-08-06 10:44