COUNT function during 0 case

• SqlServerNinja

  Ten Centuries

  Points: 1238

  More actions

  May 20, 2013 at 8:31 am

  #275372

  I have two tables

  1) Product with columns

  ProductId Name

  1 ABC

  2 DEF

  2) Sales

  TransactionId ProductId ReqTime ResTime

  1 1 05/20/2013 13:22 05/20/2013 13:23

  2 1 05/20/2013 13:22 05/20/2013 13:23

  3 1 05/20/2013 13:22 05/20/2013 13:23

  I want to get an output like this :

  ProductId TotalSales

  1 3

  2 0

  I am currently doing a query like this :

  select Distinct(ProductId), Count(TransactionId)

  from Product LEFT OUTER JOIN Transaction ON Product.ProductId=Transaction.ProductId

  where ReqTime > @BeginTime and ResTime <@EndTime

  Groupby Product.ProductId;[/code]

  I get an output like this :

  ProductId TotalSales

  1 3

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  May 20, 2013 at 8:41 am

  #1616647

  maybe something along these lines

  SELECT Product.ProductId, COUNT([Transaction].transactionId)

  FROM Product LEFT OUTER JOIN

  [Transaction] ON Product.ProductId = [Transaction].ProductId

  GROUP BY Product.ProductId

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 20, 2013 at 8:44 am

  #1616649

  or something like this:

  SELECT

  p.ProductId,

  t.TransactionCount

  FROM Product p

  LEFT JOIN (

  SELECT

  ProductId,

  TransactionCount = COUNT(*)

  FROM [Transaction]

  GROUP BY ProductId

  ) t ON t.ProductId = p.ProductId

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• SqlServerNinja

  Ten Centuries

  Points: 1238

  More actions

  May 20, 2013 at 9:16 am

  #1616659

  J Livingston SQL (5/20/2013)

  ---

  maybe something along these lines

  SELECT Product.ProductId, COUNT([Transaction].transactionId)

  FROM Product LEFT OUTER JOIN

  [Transaction] ON Product.ProductId = [Transaction].ProductId

  GROUP BY Product.ProductId

  Hey J Livingston SQL !

  Thank you !The solution works perfectly. I just edited my post. There are an additional RequestTime and ResponseTime columns . On including the where clause I again don't see the required output.

  I mean something like this :

  SELECT Product.ProductId, COUNT([Transaction].transactionId)

  FROM Product LEFT OUTER JOIN

  [Transaction] ON Product.ProductId = [Transaction].ProductId

  where [Transaction].ReqTime > @BeginTime and [Transaction].ResTime < @EndTime

  GROUP BY Product.ProductId

  This query above again doesnt include any proucts with 0 transactions .

  Is there anything that needs to be changed here ?

• SqlServerNinja

  Ten Centuries

  Points: 1238

  More actions

  May 20, 2013 at 9:21 am

  #1616662

  ChrisM@Work (5/20/2013)

  ---

  or something like this:

  SELECT

  p.ProductId,

  t.TransactionCount

  FROM Product p

  LEFT JOIN (

  SELECT

  ProductId,

  TransactionCount = COUNT(*)

  FROM [Transaction]

  GROUP BY ProductId

  ) t ON t.ProductId = p.ProductId

  Hey ! Thank you ! This solution works for me !