We help IT Professionals succeed at work.

SQL qeury with count and sum functions

flg8tor96
flg8tor96 asked
on
I have a table that has sales figures by booth.  I want to sum up the sales for each day in one column then hav e the count of the number of booths that did not report sales in the next column (sales would be zero) This query works fine but I don't know how to add the count of the number of booths with zero sales.

Select sum(S.GrossSales) as Sales, ED.EventDaysID
From Vendordb.Booth B
      Join Vendordb.Sales S on S.BoothID = B.Boothid
      Join EventDays ED on ED.EventdaysID = S.EventDaysid
Where ED.EventID = 6 and B.BoothType = 2
Group by ED.EventdaysID
Comment
Watch Question

Top Expert 2008

Commented:
try this

sum(iif(S.GrossSales=0,1,0)) as NoSalesCount

Author

Commented:
I get:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'if'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
Assuming there are records in Vendordb.Sales for each of the booths with no sales for each event day, this should work:

Select      Sum(S.GrossSales) as Sales,
      (Select Count(BoothID) From #Sales Where EventDaysID = ED.EventDaysID And GrossSales = 0) As BoothsWithNoSales,
      ED.EventDaysID
From Vendordb.Booth B
      Join Vendordb.Sales S on S.BoothID = B.Boothid
      Join EventDays ED on ED.EventdaysID = S.EventDaysid
Where ED.EventID = 6 and B.BoothType = 2
Group by ED.EventdaysID
Top Expert 2008

Commented:
are you sure you entered iif instead of if?
Oops sorry, small syntax error. Try this:

Select      Sum(S.GrossSales) as Sales,
      (Select Count(BoothID) From Vendordb.Sales Where EventDaysID = ED.EventDaysID And GrossSales = 0) As BoothsWithNoSales,
      ED.EventDaysID
From Vendordb.Booth B
      Join Vendordb.Sales S on S.BoothID = B.Boothid
      Join EventDays ED on ED.EventdaysID = S.EventDaysid
Where ED.EventID = 6 and B.BoothType = 2
Group by ED.EventdaysID
CERTIFIED EXPERT
Top Expert 2011

Commented:
sql syntax differs depending on the database you are using please always state which database (version & OS )  you are using

 the following is a generic sql example rather than the "access"   IIF example previously given

you also "need to use "  an outer join  since if th booth has zero sales then it wont have any "sales/Event" rows   for that day/event  i use distinct in the count since you've given no information on the booth table definition/structure...
so i can't assume that boothid is the primary key...

again provide background info on your table structures to allow us to give a more complete answer

good luck
Select sum(S.GrossSales) as Sales, ED.EventDaysID
      ,count(distinct case when S.boothid is null then b.boothid else null end) as Boothswithnosales
   From Vendordb.Booth B
   Left Outer Join Vendordb.Sales S 
     on S.BoothID = B.Boothid
    and B.BoothType = 2 
   Left Outer Join EventDays ED
     on ED.EventdaysID = S.EventDaysid
    and ED.EventID = 6 
  Group by ED.EventdaysID

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.