COUNT function during 0 case

Question

Post reply

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

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

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

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 !