SELECT query on one table that also includes a count and a sum total from another table

rapple · December 18, 2019, 2:24am

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!

ScottPletcher · December 18, 2019, 3:33pm

SELECT query1.MenuItemID, MI.ItemName, query1.SalesCount, query1.SalesTotal
FROM (
    SELECT MenuItemID, COUNT(SaleAmount) AS SalesCount,  SUM(SaleAmount) AS SalesTotal
    FROM dbo.TicketDetail
    GROUP BY MenuItemID
) AS query1
INNER JOIN dbo.MenuItem MI ON MI.MenuItemID = query1.MenuItemID

harishgg1 · December 18, 2019, 4:27pm

hi

i tried to do this .. i know Scott has already provided the answer ..
Just a different format ... for my exercise ...

please click arrow to the left for sample data ..

drop table MenuItem
go 


create table MenuItem
(
ID int ,
description varchar(20)
)
go 

insert into MenuItem select 1,'CheeseBurger'
insert into MenuItem select 2,'HamBurger'
go 

select * from MenuItem
go 


drop table TicketDetail
go 

create table TicketDetail
(
MenuItemID  int ,
RecordEntry DateTime ,
SaleAmount float
)
go 

insert into TicketDetail select 1,'2019-10-12 10:56:00',10.0
insert into TicketDetail select 1,'2019-10-12 11:56:00',10.0
insert into TicketDetail select 1,'2019-10-12 13:56:00',10.0
insert into TicketDetail select 1,'2019-10-12 14:56:00',10.0
insert into TicketDetail select 1,'2019-10-12 15:23:00',10.0
insert into TicketDetail select 1,'2019-10-12 15:45:00',10.0
insert into TicketDetail select 1,'2019-10-12 16:56:00',10.0
insert into TicketDetail select 1,'2019-10-12 17:56:00',10.0

insert into TicketDetail select 2,'2019-10-12 09:56:00',5.0
insert into TicketDetail select 2,'2019-10-12 11:56:00',5.0
insert into TicketDetail select 2,'2019-10-12 13:45:00',5.0
insert into TicketDetail select 2,'2019-10-12 14:12:00',5.0
insert into TicketDetail select 2,'2019-10-12 15:10:00',5.0

go

select 'Join Data',* from MenuItem a join TicketDetail b on a.ID = b.MenuItemID

select 
  'SQL Output', b.MenuItemID,a.description,count(MenuItemID) as SalesCount ,sum(SaleAmount) as SalesTotal 
from MenuItem a
     join TicketDetail b 
         on a.ID = b.MenuItemID
group by b.MenuItemID,a.description

rapple · December 18, 2019, 7:50pm

Oh my gosh guys THANK YOU!! Both of those options work great for me. I wish I had come here before trying for hours to figure it out.

Thanks again.
rapple