Stored Procedure help
-
Ok, I'm trying to create a stored procedure that will count the certain conditions and display those counts. Here's what I have.
CREATE PROCEDURE smtptest AS
SELECT SUM(CASE WHEN [cs-bytes] > 200 AND [cs-bytes] < 2000 THEN 1 ELSE 0 end) AS 'text',
SUM(CASE WHEN [cs-bytes] > 10000 AND [cs-bytes] < 90000 THEN 1 ELSE 0 end) AS 'photo',
SUM(CASE WHEN [cs-bytes] > 250000 THEN 1 ELSE 0 end) AS 'video'
FROM smtp2 WHERE [cs-username] like 'mc______' and [cs-method] = 'data' GO
This outputs three columns with values. What I am looking to add is another column based on the [cs-username] field. I want the field to display all the "distinct" [cs-username]'s that are like 'mc______' and their corresponding 'text', 'photo' and 'video' columns.
I have approximately 10 'mc_____' units and that number is going to grow so I can't perform functions on them individually. This is where I get stuck trying to figure out how to display the fourth column.
-
I think all you need is to add cs-username to the SELECT and add a GROUP BY cs-username. Like this:
SELECT
[cs-username],
SUM(CASE WHEN [cs-bytes] > 200 AND
[cs-bytes] < 2000 THEN 1
ELSE 0
END) AS 'text',
SUM(CASE WHEN [cs-bytes] > 10000 AND
[cs-bytes] < 90000 THEN 1
ELSE 0
END) AS 'photo',
SUM(CASE WHEN [cs-bytes] > 250000 THEN 1
ELSE 0
END) AS 'video'
FROM
smtp2
WHERE
[cs-username] LIKE 'mc______' AND
[cs-method] = 'data'
GROUP BY
[cs-username];Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Ha ha. I clicked on Latest Posts and this showed up and it is 16 years old and I replied.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question - Jack Corbett - Wednesday, April 5, 2017 8:33 AM
They say it's never too late. But the reply might have been.
:hehe: - Jack Corbett - Wednesday, April 5, 2017 8:33 AM
LOL just looked at the post ID for the original post. It is 1205 where the current ones are 1.8 million something.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply