• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

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
0
flg8tor96
Asked:
flg8tor96
1 Solution
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now