Menu
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?
374
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
SQL Server has a couple of inbuilt functions to get the day of week from the given date. To get the name of the day of week, you can use DATENAME function and to get the number of the day of week, you can use DATEPART function.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
