I have something like that database structure:
TABLE_PRODUCT
| ID | NAME | FK_CATEGORY |
| 1 | Product 1 | 1 |
| 2 | Product 2 | 1 |
| 3 | Product 3 | 2 |
TABLE_CATEGORY
| ID | NAME |
| 1 | Category 1 |
| 2 | Category 2 |
TABLE_COUNTRY
| ID | NAME |
| 1 | America |
| 2 | UK |
| 3 | Portugal |
| 4 | France |
TABLE_ORDER
| ID | DATE | FK_CUSTOMER | FK_COUNTRY |
| 1 | 10/01/2024 | 1 | 1 |
| 2 | 10/01/2024 | 2 | 1 |
| 3 | 10/01/2024 | 3 | 1 |
| 4 | 15/01/2024 | 4 | 2 |
| 5 | 20/01/2024 | 5 | 2 |
| 6 | 10/02/2024 | 6 | 2 |
| 7 | 12/02/2024 | 7 | 3 |
| 8 | 05/03/2024 | 8 | 4 |
TABLE_ORDER_ITEM
| ID | FK_ORDER | FK_PRODUCT | QUANTITY | TOTAL AMOUNT |
| 1 | 1 | 1 | 1 | 10 |
| 2 | 1 | 2 | 2 | 20 |
| 3 | 1 | 3 | 2 | 20 |
| 4 | 2 | 1 | 1 | 10 |
| 5 | 3 | 1 | 4 | 40 |
| 6 | 3 | 2 | 2 | 20 |
| 7 | 4 | 1 | 1 | 10 |
| 8 | 4 | 2 | 3 | 30 |
| 9 | 4 | 3 | 5 | 50 |
| 10 | 5 | 1 | 4 | 40 |
| 11 | 5 | 2 | 3 | 30 |
| 12 | 5 | 3 | 2 | 20 |
| 13 | 6 | 1 | 5 | 50 |
| 14 | 6 | 2 | 4 | 40 |
| 15 | 6 | 3 | 3 | 30 |
| 16 | 7 | 1 | 3 | 30 |
| 17 | 7 | 2 | 2 | 20 |
| 18 | 7 | 3 | 1 | 10 |
| 19 | 8 | 1 | 2 | 20 |
| 20 | 8 | 2 | 2 | 20 |
| 21 | 8 | 3 | 2 | 20 |
I'd like generate a summary view and I'd like have summary of sales by date, by country, by country and date, by month, by country month with:
Total value sold per category
Total quantity sold per category
Total value sold
Total quantity sold
e.g.
Sumary sales per day
| Date | Category | Total quantity sold per category | Total value sold per category |
| 1/10/2024 | Category 1 | 10 | 100 |
| 1/10/2024 | Category 2 | 2 | 20 |
| 15/1/2024 | Category 1 | 4 | 40 |
| 15/1/2024 | Category 2 | 5 | 50 |
| 20/01/2024 | Category 1 | 7 | 70 |
| 20/01/2024 | Category 2 | 2 | 20 |
| 2/10/2024 | Category 1 | 9 | 90 |
| 2/10/2024 | Category 2 | 3 | 30 |
| 2/12/2024 | Category 1 | 5 | 50 |
| 2/12/2024 | Category 2 | 1 | 10 |
| 3/5/2024 | Category 1 | 4 | 40 |
| 3/5/2024 | Category 2 | 2 | 20 |
| Total | | 54 | 540 |
Sumary sales per Month
| Date | Category | Total quantity sold per category | Total value sold per category |
| Jan-2024 | Category 1 | 21 | 210 |
| Jan-2024 | Category 2 | 9 | 90 |
| Total Jan-2024 | | 30 | 300 |
| Feb-2024 | Category 1 | 14 | 140 |
| Feb-2024 | Category 2 | 4 | 40 |
| Total Feb-2024 | | 18 | 180 |
| Mar-2024 | Category 1 | 4 | 40 |
| Mar-2024 | Category 2 | 2 | 20 |
| Total Mar-2024 | | 6 | 60 |
Sumary sales per day and Country
| Date | Category | Total quantity sold per category | Total value sold per category |
| Amรฉdica | | 6 | 60 |
| 1/10/2024 | Category 1 | 4 | 40 |
| 1/10/2024 | Category 2 | 2 | 20 |
| Portugal | | 12 | 120 |
| 1/10/2024 | Category 1 | 6 | 60 |
| 2/12/2024 | Category 1 | 5 | 50 |
| 2/12/2024 | Category 2 | 1 | 10 |
| UK | | 30 | 300 |
| 15/1/2024 | Category 1 | 4 | 40 |
| 15/1/2024 | Category 2 | 5 | 50 |
| 20/01/2024 | Category 1 | 7 | 70 |
| 20/01/2024 | Category 2 | 2 | 20 |
| 2/10/2024 | Category 1 | 9 | 90 |
| 2/10/2024 | Category 2 | 3 | 30 |
| France | | 6 | 60 |
| 3/5/2024 | Category 1 | 4 | 40 |
| 3/5/2024 | Category 2 | 2 | 20 |
Can anyone help me with this? Thank you.
LinkedIn
Twitter
