public.sales_by_store
Documentation | Pagila |
Schema | public |
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 | text | Information about store - city and country. | |||||
Information about store - city and country. |
||||||||
2 | manager | text | The staff member full name. | |||||
The staff member full name. |
||||||||
3 | total_sales | numeric | The total sale. | |||||
The total sale. |
Uses
Name |
---|
public.sales_by_store
|
Script
SELECT (((c.city)::text || ','::text) || (cy.country)::text) AS store, (((m.first_name)::text || ' '::text) || (m.last_name)::text) AS manager, sum(p.amount) AS total_sales FROM (((((((payment p JOIN rental r ON ((p.rental_id = r.rental_id))) JOIN inventory i ON ((r.inventory_id = i.inventory_id))) JOIN store s ON ((i.store_id = s.store_id))) JOIN address a ON ((s.address_id = a.address_id))) JOIN city c ON ((a.city_id = c.city_id))) JOIN country cy ON ((c.country_id = cy.country_id))) JOIN staff m ON ((s.manager_staff_id = m.staff_id))) GROUP BY cy.country, c.city, s.store_id, m.first_name, m.last_name ORDER BY cy.country, c.city; |
Exported: 2018-10-12 17:07, Last imported: 2018-08-06 09:25