October 3, 2017 at 9:55 am
Hi,
Need to select count of rows based on codition from a table.
Tried with this query , but no luck.
select
CASE WHEN TYPE ='D' THEN count(TYPE) end AS 'TotalXRows',
CASE WHEN TYPE ='L' THEN count(TYPE) end AS 'TotalYRows',
Count(TYPE) AS 'TotalRows'
from dbo.Table (nolock)
RESULT should be like below.
TotalXRows TotalYRows TotalRows(TotalXRows+TotalYRows)
Any help here.
Thank you.
October 3, 2017 at 10:01 am
select
COUNT(CASE WHEN TYPE ='D' THEN 1 ELSE NULL end) AS 'TotalXRows',
COUNT(CASE WHEN TYPE ='L' THEN 1 ELSE NULL end) AS 'TotalYRows',
Count(TYPE) AS 'TotalRows'
from dbo.Table (nolock)
October 3, 2017 at 10:06 am
select
SUM(CASE WHEN TYPE ='D' THEN 1 ELSE 0 END) AS 'TotalXRows',
SUM(CASE WHEN TYPE ='L' THEN 1 ELSE 0 END) AS 'TotalYRows',
SUM(1) AS 'TotalRows'
from dbo.Table with (nolock)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 3, 2017 at 10:16 am
SELECT -- flatten to one row
MAX(CASE WHEN [TYPE] = 'D' THEN cnt END) AS 'TotalXRows',
MAX(CASE WHEN [TYPE] = 'L' THEN cnt END) AS 'TotalYRows',
SUM(cnt) AS 'TotalRows'
FROM (-- two rows very quickly
SELECT [TYPE], cnt = COUNT(*)
FROM dbo.Table
WHERE [TYPE] IN ('D','L')
GROUP BY [TYPE]
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2017 at 10:18 am
Thanks a lot.
October 3, 2017 at 12:53 pm
I decided to run a comparison of counting using COUNT vs SUM. My impulse is to use COUNT to count, but I know that many people prefer to use SUM and I was wondering if there was any significant difference between the two, so I decided to run a test.
Here is the (obfuscated) query that I usedDECLARE @times TABLE (
t datetime2 DEFAULT sysdatetime(),
cmt VARCHAR(50)
)
INSERT @times(cmt)
VALUES('Begin')
SELECT COUNT(CASE WHEN SomeField = 'SomeValue' THEN 1 END)
FROM SomeTable
INSERT @times(cmt)
VALUES('COUNT')
SELECT SUM(CASE WHEN SomeField = 'SomeValue' THEN 1 END)
FROM SomeTable
INSERT @times(cmt)
VALUES('SUM w/ NULLs')
SELECT SUM(CASE WHEN SomeField = 'SomeValue' THEN 1 ELSE 0 END)
FROM SomeTable
INSERT @times(cmt)
VALUES('SUM w/ 0s')
SELECT *, DATEDIFF(MICROSECOND, LAG(t) OVER(ORDER BY t), t) AS duration
FROM @times
Here are the results:
cmt duration
COUNT 463186477
SUM w/ NULLs 465377630
SUM w/ 0s 461163335
This was only run once, so the differences may not be significant.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2017 at 7:42 am
SUM(CASE WHEN foobar_type = 'D' THEN 1 ELSE 0 END) AS d_count,
SUM(CASE WHEN foobar_type = 'L' THEN 1 ELSE 0 END) AS l_count
Please post DDL and follow ANSI/ISO standards when asking for help.
October 4, 2017 at 10:26 am
bartedgerton - Tuesday, October 3, 2017 10:01 AMYou have a fundamental problem; you don't understand that SQL has a CASE expression, not a CASE statement. Expressions return a scalar value. You also don't know that "type" is what ANSI ISO 11179 calls an attribute property; it has to be the type of something, not a universal, magic, generic free-floating "type". One possible way to write this would be:SUM(CASE WHEN foobar_type = 'D' THEN 1 ELSE 0 END) AS d_count,
SUM(CASE WHEN foobar_type = 'L' THEN 1 ELSE 0 END) AS l_count
Another post you can ignore. Apparently, Joe has forgotten that the COUNT function doesn't count NULL values, and bartedgerton's originally posted code would be functionally equivalent. I prefer the SUM method, but unless you've got severe performance problems with such a query, there's not usually enough of a difference in favor of SUM to waste the time.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 12:50 pm
All of the core aggregates in SQL drop NULLs, including SUM(). And , yes, as Drew found out, a modern optimizer will recognize the "aggregate> (<case expression>) " pattern, so they handled the same way.
>> I prefer the SUM method, but unless you've got severe performance problems with such a query, there's not usually enough of a difference in favor of SUM to waste the time.<<
I also favor "SUM(<case expression>)" over the "COUNT(<case expression>)" template. But that's because I've written optimizers. I know that if I use the one-zero CASE options, the optimizer will give me the same output as the COUNT() template. However, if my CASE expression is more complicated, such as having weights in the THEN clauses of the CASE expression, then I have a huge advantage in maintaining and writing my code.
Steve, you do realize I spent 10 years of my life in the standards committee for this language, over 50 years in the IT trade (first paying job in 1965! FORTRAN for DoD; science fair prize job), much of it working for SQL engine vendors (Joe, translate ANSI/ISO standards into programming specs)? And that a lot of that time has been spent teaching people how to use the language I helped developed? Yes, I know I can be annoyingly pedantic. I hold people to my standards, and I want them to be professional database people.
I just can't get into your world of "hi, I'm Larry the Cable Guy, let's get her done!"' I assume that when someone post something on a forum. They are actually trying to learn as opposed to just get a quick kludge. This is an assumption on my part; a lot of people posting on SQL forums really do just want to kludge. They're not database people, they never wanted to be database people and they'll never have to do much more than this single problem for the rest of their job.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 4, 2017 at 1:05 pm
bartedgerton - Tuesday, October 3, 2017 10:01 AM>> Another post you can ignore. Apparently, Joe has forgotten that the COUNT function doesn't count NULL values, and bartedgerton's originally posted code would be functionally equivalent. <<All of the core aggregates in SQL drop NULLs, including SUM(). And , yes, as Drew found out, a modern optimizer will recognize the "aggregate> (<case expression>) " pattern, so they handled the same way.
>> I prefer the SUM method, but unless you've got severe performance problems with such a query, there's not usually enough of a difference in favor of SUM to waste the time.<<
I also favor "SUM(<case expression>)" over the "COUNT(<case expression>)" template. But that's because I've written optimizers. I know that if I use the one-zero CASE options, the optimizer will give me the same output as the COUNT() template. However, if my CASE expression is more complicated, such as having weights in the THEN clauses of the CASE expression, then I have a huge advantage in maintaining and writing my code.
Steve, you do realize I spent 10 years of my life in the standards committee for this language, over 50 years in the IT trade (first paying job in 1965! FORTRAN for DoD; science fair prize job), much of it working for SQL engine vendors (Joe, translate ANSI/ISO standards into programming specs)? And that a lot of that time has been spent teaching people how to use the language I helped developed? Yes, I know I can be annoyingly pedantic. I hold people to my standards, and I want them to be professional database people.
I just can't get into your world of "hi, I'm Larry the Cable Guy, let's get her done!"' I assume that when someone post something on a forum. They are actually trying to learn as opposed to just get a quick kludge. This is an assumption on my part; a lot of people posting on SQL forums really do just want to kludge. They're not database people, they never wanted to be database people and they'll never have to do much more than this single problem for the rest of their job.
Whether we like it or not, Joe, the real world isn't going to just accommodate all standards and be full of people sufficiently trained to do the job right the first time. Don't join that world if you don't want to, but frankly, you're not really helping most of the time precisely because you come across as annoyingly pedantic. And yes, I'm aware of your background. Given the perception you keep creating, it's actually going to be hard for some people to believe you actually have that background. To be quite honest, someone here said you're actually quite a nice guy in person, but given the perception you've created, I'm not all that interested in meeting you. And I wouldn't be at all surprised if others hold similar perceptions. And yes, I know that perception is not always reality, but for most of the world, the opposite is the unfortunate truth. Time to deal with that fact, Joe. The rest of us need to in order to get paid.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 4, 2017 at 1:33 pm
I prefer to use COUNT because I think it is more readable. I usually use in the 2012 compatibility, which seems to be the easiest to understand/most readable, to me, anyway:
select
COUNT(IIF(TYPE ='D',1,NULL)) AS 'TotalXRows',
COUNT(IIF(TYPE ='L' ,1,NULL)) AS 'TotalYRows',
COUNT(1) AS 'TotalRows'
from dbo.Table (nolock)
If you do use SUM, I would recommend to use zero instead of NULL as SUM seems to run faster when there are no nulls in the data.
October 4, 2017 at 1:34 pm
bartedgerton - Tuesday, October 3, 2017 10:01 AM>> Another post you can ignore. Apparently, Joe has forgotten that the COUNT function doesn't count NULL values, and bartedgerton's originally posted code would be functionally equivalent. <<All of the core aggregates in SQL drop NULLs, including SUM(). And , yes, as Drew found out, a modern optimizer will recognize the "aggregate> (<case expression>) " pattern, so they handled the same way.
>> I prefer the SUM method, but unless you've got severe performance problems with such a query, there's not usually enough of a difference in favor of SUM to waste the time.<<
I also favor "SUM(<case expression>)" over the "COUNT(<case expression>)" template. But that's because I've written optimizers. I know that if I use the one-zero CASE options, the optimizer will give me the same output as the COUNT() template. However, if my CASE expression is more complicated, such as having weights in the THEN clauses of the CASE expression, then I have a huge advantage in maintaining and writing my code.
Steve, you do realize I spent 10 years of my life in the standards committee for this language, over 50 years in the IT trade (first paying job in 1965! FORTRAN for DoD; science fair prize job), much of it working for SQL engine vendors (Joe, translate ANSI/ISO standards into programming specs)? And that a lot of that time has been spent teaching people how to use the language I helped developed? Yes, I know I can be annoyingly pedantic. I hold people to my standards, and I want them to be professional database people.
I just can't get into your world of "hi, I'm Larry the Cable Guy, let's get her done!"' I assume that when someone post something on a forum. They are actually trying to learn as opposed to just get a quick kludge. This is an assumption on my part; a lot of people posting on SQL forums really do just want to kludge. They're not database people, they never wanted to be database people and they'll never have to do much more than this single problem for the rest of their job.
The issue, Joe, is that you're not trying to teach, you're trying to indoctrinate. You have very strong feelings about how SQL should be, but you ignore the realities of how the dialects (specifically T-SQL) actually operate. You are what linguists call a prescriptivist, while most of us here are descriptivists.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 4, 2017 at 2:06 pm
bartedgerton - Wednesday, October 4, 2017 1:33 PMI prefer to use COUNT because I think it is more readable. I usually use in the 2012 compatibility, which seems to be the easiest to understand/most readable, to me, anyway:select
COUNT(IIF(TYPE ='D',1,NULL)) AS 'TotalXRows',
COUNT(IIF(TYPE ='L' ,1,NULL)) AS 'TotalYRows',
COUNT(1) AS 'TotalRows'
from dbo.Table (nolock)If you do use SUM, I would recommend to use zero instead of NULL as SUM seems to run faster when there are no nulls in the data.
IIF should never be allowed in SQL. It's too foreign an approach, and thus much more difficult to understand if you're not a developer in a language that uses it. And SQL shouldn't be corrupted just to "simplify" things for developers. CASE is straightforward enough even for non-developers, IIF isn't.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 5, 2017 at 6:18 am
ScottPletcher - Wednesday, October 4, 2017 2:06 PMbartedgerton - Wednesday, October 4, 2017 1:33 PMI prefer to use COUNT because I think it is more readable. I usually use in the 2012 compatibility, which seems to be the easiest to understand/most readable, to me, anyway:select
COUNT(IIF(TYPE ='D',1,NULL)) AS 'TotalXRows',
COUNT(IIF(TYPE ='L' ,1,NULL)) AS 'TotalYRows',
COUNT(1) AS 'TotalRows'
from dbo.Table (nolock)If you do use SUM, I would recommend to use zero instead of NULL as SUM seems to run faster when there are no nulls in the data.
IIF should never be allowed in SQL. It's too foreign an approach, and thus much more difficult to understand if you're not a developer in a language that uses it. And SQL shouldn't be corrupted just to "simplify" things for developers. CASE is straightforward enough even for non-developers, IIF isn't.
Scott ... just curious if you know of anyone testing performance on whether a CASE expression would perform any differently than a functionally equivalent IIF, or if you know what SQL Server does with IIF internally. I suspect it may turn it into the equivalent CASE expression, but unfortunately, it just seems that every time I come across something like this, it's when I have the least possible time available to test it out and see. Maybe Jeff Moden can apply one of his test rigs to this one.... Jeff?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 5, 2017 at 6:45 am
sgmunson - Thursday, October 5, 2017 6:18 AMScottPletcher - Wednesday, October 4, 2017 2:06 PMbartedgerton - Wednesday, October 4, 2017 1:33 PMI prefer to use COUNT because I think it is more readable. I usually use in the 2012 compatibility, which seems to be the easiest to understand/most readable, to me, anyway:select
COUNT(IIF(TYPE ='D',1,NULL)) AS 'TotalXRows',
COUNT(IIF(TYPE ='L' ,1,NULL)) AS 'TotalYRows',
COUNT(1) AS 'TotalRows'
from dbo.Table (nolock)If you do use SUM, I would recommend to use zero instead of NULL as SUM seems to run faster when there are no nulls in the data.
IIF should never be allowed in SQL. It's too foreign an approach, and thus much more difficult to understand if you're not a developer in a language that uses it. And SQL shouldn't be corrupted just to "simplify" things for developers. CASE is straightforward enough even for non-developers, IIF isn't.
Scott ... just curious if you know of anyone testing performance on whether a CASE expression would perform any differently than a functionally equivalent IIF, or if you know what SQL Server does with IIF internally. I suspect it may turn it into the equivalent CASE expression, but unfortunately, it just seems that every time I come across something like this, it's when I have the least possible time available to test it out and see. Maybe Jeff Moden can apply one of his test rigs to this one.... Jeff?
If you look at Node 3 of the execution plan for this statement
SELECT
COUNT(IIF(System_Type_Id =127,1,NULL)) AS 'TotalXRows'
FROM SYS.COLUMNS
it's a compute scalar, with this expression:
[Expr1018] = Scalar Operator(CASE WHEN [MyDB].[sys].[syscolpars].[xtype]=(127) THEN (1) ELSE NULL END)
EDIT: JIC version is significant, this is with 2012.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply