COUNT function during 0 case

  • 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

  • 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

  • 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

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

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply