Select COUNT(DISTINCT...) issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select COUNT(DISTINCT...) issue

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-20 : 05:59:16

Hello,

I want to select some users of Users table and count the offers published in Offers table by every selected user for a specified date, the selected users that hasn’ t got offers published for that date show 0 (zero).
I want this kind of selection to insert into another table the specified date, the user number, number of offers published that date, and other data. How can I do it?

I tried:


USE database_4
GO

Declare @Date As smalldatetime
SET @Date = DATEADD(dd, 0, DATEDIFF(dd, 1, getdate()))  --Yesterday date, without time

  SELECT @Date As 'Date', u.User_id As 'User_Id', COUNT(DISTINCT offe.Offer_id) As Offers, ‘Other Data’, ‘Other Data’,..

FROM Users As u

LEFT JOIN Offers As offe
On u.User_id = offe.User_num

WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, offe.Date)) = @Date And offe.State <> 5 And u.Activity = 1  
GROUP BY u.User_id

GO

In this way works partially, only are selected the users that published offers in the specified date, but I also want to show the rest of selected users (u.Activity = 1) and show 0 (zero) offers in Offers column. How can I achieve it?

Thank you,
Cesar

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-20 : 07:58:14

LEFT JOIN Offers As offe
On u.User_id = offe.User_num
and DATEADD(dd, 0, DATEDIFF(dd, 0, offe.Date)) = @Date
And offe.State <> 5
where u.Activity = 1
GROUP BY u.User_id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cesark
Posting Yak Master

215 Posts

Posted - 2007-09-20 : 11:11:52

Works fine! Thank you nr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources