SQL qeury with count and sum functions

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
flg8tor96Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ee_rleeCommented:
try this

sum(iif(S.GrossSales=0,1,0)) as NoSalesCount
0
flg8tor96Author 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 ','.
0
thecodistCommented:
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
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

ee_rleeCommented:
are you sure you entered iif instead of if?
0
thecodistCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.