Summary of Counts

Question

Post reply

Summary of Counts

Gerry Dyck-312283

Valued Member

Points: 57
More actions
August 21, 2006 at 2:54 pm

#114093

Hello, I am new to T-SQL and need to create a query but am having trouble understanding how to get what I am looking for.
I have a table with the following:
Client varChar(100),
LogDate smalldatetime,
Status char(20),
Digital bit,
The Status field will be Processed, Not Processed or On Hold.
In any given month the table will have 1 or 2 hundred records added at some state of processing. I need to be able to create a query that would return counts of various items by Client. Ideally, the query would return the following fields for each Client for each month in a given year:
Client,
Month,
Count of total records,
Count of total records with Status = "Processed"
Count of total records with Status = "Not Processed"
Count of total records with Status = "On Hold"
Count of total records with Digital = 0
Count of total records with Digital = 1
I can easily generate a query that gives the count of records for each possiblity (that is count of status = "Processing" AND Digital = 0,status = "Processing" AND Digital = 1, status = "Not Processed" AND Digital = 0.....) but this leaves me with more processing to do in my VB.NET code.
I believe there is a way of doing this in T-SQL. Of course, if it is over my head then I will just do it in VB but it would seem more elegant to get the database to do the work.
Thank you for your help.
Gerry Dyck

Viewing 7 posts - 1 through 6 (of 6 total)

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

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

SELECT Client,

DATEPART(MONTH, LogDate),

COUNT(*) [Total Records],

SUM(CASE WHEN Status = 'Processed' THEN 1 ELSE 0 END) [Total Processed],

SUM(CASE WHEN Status = 'Not Processed' THEN 1 ELSE 0 END) [Total Not Processed],

SUM(CASE WHEN Status = 'On Hold' THEN 1 ELSE 0 END) [Toatl On Hold],

SUM(CASE WHEN Digital = 0 THEN 1 ELSE 0 END) [Toatl Digital 0],

SUM(CASE WHEN Digital = 1 THEN 1 ELSE 0 END) [Toatl Digital 1]

FROM

YourTable

WHERE

LogDate >= @FromDate AND LogDate <= @ToDate

GROUP BY

Client,

DATEPART(MONTH, LogDate)

Note if the query goes across years it will add months data together. (Ex. Jan 2005, Jan 2006 wil be added)

Regards,
gova

Gerry Dyck-312283 Valued Member Points: 57 More actions · Answer 2

Wow govi, that was fast. Thank you very much. I will try this out. I was on the right track but getting frustrated! I was just attempting to use COUNT where you have SUM. I will let you know if it works.

Thanks again,

Gerry

Gerry Dyck-312283 Valued Member Points: 57 More actions · Answer 3

govi, It didn't work for me! I get a syntax error near ")" on the line with the first SUM on it.

When I get it working, I will be passing the query an @year value to limit to one year. But that will come later!

BTW, I am using SQL Express and not SQL Server 7. I just realized this and I hope I didn't go to the wrong forum.

Thanks,

Gerry

Gerry Dyck-312283 Valued Member Points: 57 More actions · Answer 4

I got it govi! I needed to put the keyword END before the bracket in order to complete the CASE statement.

Thanks Again and have a great day,

Gerry Dyck

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 5

Sorry I did not check the code before posting. Good you figured it

HTH.

Regards,
gova

Gerry Dyck-312283 Valued Member Points: 57 More actions · Answer 6

That's just fine govi. Whenever I ask questions in various forums I am always aware that, while it is nice to get a complete and correct answer, what is most important is to learn something. In this case I became aware of the CASE statement that could be used in a SUM but I had trouble determining how to use it. Most often the samples that can be easily found are too simple for the application needed. After seeing your solution, I was able to easily see how to use the CASE statement. After further digging, I see that for my situation I am counting those records that meet the criteria, hence the THEN 1 ELSE 0. I can just as easily put another expression in place of the 1 or 0 like a calculation based on other fields. I know of several other points in my program where the SUM(CASE) could have been used instead my clunky methods. I will be returning to those and changing them.

I am a self taught VB and SQL programmer and the older I get the more I see how much I don't know. Thanks again for your help. I will definitely be back with further questions.

Gerry