I have two tables in a point of sale system that I need information from in a single query. The first table is MenuItem, which contains specific information for food menu items. The other table is TicketDetail, which includes a SaleAmount field. The tables can are inner joined between the ID field in MenuItem and a MenuItemID field in TicketDetail.
I need to run a select query that returns certain fields from the Item table along with a total count and a total SaleAmount for each item.
Example: Let's say I only sell two things, a cheeseburger and a hamburger. There is a record in my MenutItem table for Cheese Burger with a primary key ID of 1, and one for Hamburger, with an ID of 2. In my TicketDetail table, there are 50 records with the MenuItemID for cheese burger, and 20 records with the MenuItemID for hamburger. The SaleAmount for each record varies depending on time of day (lunch/dinner), but let's say for this example that all the TicketDetail records for Cheese Burger are 10.00, and all the Hamburgers are 5.00.
I would like to build a select query on the MenuItem table joined to the TicketDetail table that shows the number of records in TicketDetail for each MenuItem, and a SUM of the SalesAmount field in TicketDetail for each item.
So the results I need would look like this:
ID ItemName SalesCount SalesTotal
1 Cheeseburger 50 500.00
2 Hamburger 20 100.00
I feel like this maybe could be done with a union query, but I don't have a lot of experience with those.
A push in the right direction would be appreciated. Thank you for your time!