Hi,
I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following
1. The wine name, grape variety, year, winery, and region
2. The minimum cost of wine in the inventory
3. The number of bottles available at the minimum price
4. The total number of bottles available at any price
5. The total number of unique customers who purchased the wine (at any price)
6. A breakdown of the sales revenue, stock sold, and unique customers for each price that the wine can be sol for.
So, far I have done successfully to join those queries in a single table till the fourth within one SQL query
I have done the fifth query in a single SQL query, without having connected with the last four previous queries.
I encountered problem to join the fifth query into the above complex query. The table rows appeared to be imbalance.
Winestore database data is available from this link, if you wish to have a look http://stevenryan.co.c c/source/other/winestore.data
Any help leading to my success to join all those six queries into a single table and in single SQL query would be appreciated. Thank you so much.
I am having trouble with some complex SQL queries. I’ve got winestore database, taken from Web Database Application with PHP and MySQL book. And some question about queries as the following
1. The wine name, grape variety, year, winery, and region
2. The minimum cost of wine in the inventory
3. The number of bottles available at the minimum price
4. The total number of bottles available at any price
5. The total number of unique customers who purchased the wine (at any price)
6. A breakdown of the sales revenue, stock sold, and unique customers for each price that the wine can be sol for.
So, far I have done successfully to join those queries in a single table till the fourth within one SQL query
Code:
SELECT wine.wine_name, grape_variety.variety, wine.year, winery.winery_name, region.region_name, inventory.cost, inventory.on_hand, SUM(inventory.on_hand) FROM wine INNER JOIN grape_variety INNER JOIN wine_variety ON wine.wine_id = wine_variety.wine_id AND wine_variety.variety_id = grape_variety.variety_id JOIN winery ON wine.winery_id = winery.winery_id JOIN region ON region.region_id = winery.region_id JOIN inventory ON inventory.wine_id = wine.wine_id GROUP BY inventory.wine_id, grape_variety.variety_id;
Code:
SELECT wine_id, COUNT(items.cust_id) from items GROUP BY wine_id;
Winestore database data is available from this link, if you wish to have a look http://stevenryan.co.c c/source/other/winestore.data
Any help leading to my success to join all those six queries into a single table and in single SQL query would be appreciated. Thank you so much.