SQL group by day, with count

Question

I've got a log table in SQL Server that looks like this:

CREATE TABLE [dbo].[RefundProcessLog](
 [LogId] [bigint] IDENTITY(1,1) NOT NULL,
 [LogDate] [datetime] NOT NULL,
 [LogType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [RefundId] [int] NULL,
 [RefundTypeId] [smallint] NULL,
 [LogMessage] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 [LoggedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_RefundProcessLog] PRIMARY KEY CLUSTERED 
(
 [LogId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO

What I want is a list of results that represents how many different refundids were processed each day, throwing out any NULLs.

What SQL would I need to write to produce these results?

Erdmann

Answer 1

I like this approach in (MS SQL):

SELECT 
  Convert(char(8), LogDate, 112),
  count(distinct RefundId)
FROM RefundProcessing
GROUP BY Convert(char(8), LogDate, 112)

Urbina

Answer 2

select cast(LogDate as date) as LogDate, count(refundId) as refundCount
from yourTable
group by cast(LogDate as date)

Depending on the dialect of SQL you're using, you may have to change the CAST to something else. The expression should convert the LogDate to a date-only value.

Also, if you say "different refundId" because there could be repeated values of refundId that you only want to count once, use count(DISTINCT refundId)

Lorrielorrimer

The performance of this should be far better than the accepted solution, which involves a string conversion. – Downstream

@Downstream Agree about the performance versus string conversion. But do you (or anyone) know about performance versus the solution from charles-bretana that uses DateAdd(day, 0, DateDiff(day, 0, LogDate)) – Lenard

@Fonnae, I haven't measured, but I would expect this to be slightly faster than that other answer because that's doing some generic date manipulations (which are probably slower than this CAST), and I'm pretty sure those operations result in a full DATETIME with midnight as the time, whereas this solution does the grouping using the smaller DATE type, which should be faster to work with. – Downstream

Answer 3

What database vendor are you using? Whichever it is, replace the "DateOnly(LogDate)" in the following with the appropriate construict to extract the date portion (strip off the time) from the logdate column value and then try this:

Select [DateOnly(LogDate)], Count Distinct RefundId
From RefundProcessLog
Group By [DateOnly(LogDate)]

In Sql server, for e.g., the appropriate construct would be:

Select DateAdd(day, 0, DateDiff(day, 0, LogDate)), Count(Distinct RefundId)
From RefundProcessLog
Group By DateAdd(day, 0, DateDiff(day, 0, LogDate))

Coral

Answer 4

SELECT COUNT(RefundId), DateOnly(LogDate) LoggingDate
FROM RefundProcessLog
GROUP BY DateOnly(LogDate)

"DateOnly" is specific to your SQL database, which you haven't specified.

For SQL Server you could use DateAdd(dd,0, DateDiff(dd,0,LogDate)) for "DateOnly"

Scriptwriter

You probably want to include the date in the result set. As written, this will give the numbers of refundIds, but there will be no way to know which number occurs on what day. – Lorrielorrimer

Answer 5

SQL Server 2008 introduced the date datatype which makes the following possible:

select convert(date, LogDate),
      ,count(refundid) AS 'refunds'
  from RefundProcessing
group by convert(date,LogDate)
order by convert(date,LogDate)

Newmark

Answer 6

In SqlServer, it would be something like:

select datepart(YEAR, [LogDate]), datepart(MONTH, [LogDate]), datepart(DAY, [LogDate]), count(refundid) as [Count]
from [RefundProcessing]
group by datepart(YEAR, [LogDate]), datepart(MONTH, [LogDate]), datepart(DAY, [LogDate])

Warrantee

Answer 7

Select count(*), LogDate, refundid from RefundProcessLog
where refundid is not null
group by LogDate, refundid

Edit:

Or drop RefundID if you don't want it broken down by refunds

Stakeout

This won't work. It will return a row with a count for each distinct value of refundid, which is not what's wanted. – Lorrielorrimer