May 9, 2012 at 5:38 pm
Hi,
Can you please tell me how to get the below result set in single record
EX:
countofsuccess countoffailure
5 6
below is the script am using
select
case when void = 0 then count(ID) end as Countofsuccess,
case when void = 1 then count (ID) end as countoffailure
from test
group by
void
result set using above script
countofsuccess countoffailure
5 NULL
NULL 6
thanks.............
May 9, 2012 at 5:48 pm
select
sum(1-void) as Countofsuccess,
sum(void) as countoffailure
from test
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 9, 2012 at 5:54 pm
Iam getting the following error when used executing the query
select
sum(1-void) as Countofsuccess,
sum(void) as countoffailure
from test
Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for sum operator.
May 9, 2012 at 6:42 pm
How about something like this?
SELECT
SUM(CASE WHEN void = 0 THEN 1 ELSE 0 END) countOfSuccess,
SUM(CASE WHEN void = 1 THEN 1 ELSE 0 END) countOfFailure
FROM test
May 9, 2012 at 7:17 pm
Three other options are:
SELECT CountofFailure = SUM(CAST(void AS INT))
,CountofSuccess = SUM(CAST(~void AS INT))
FROM test
SELECT CountofFailure = SUM(0 + void)
,CountofSuccess = SUM(1 - void)
FROM test
SELECT CountofFailure = SUM(ABS(void))
,CountofSuccess = SUM(ABS(~void))
FROM test
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 10, 2012 at 5:02 am
so how should we use this statement without returning 2 records
select
case when Void = 0 then SUM(weight) end AS totalweight
from test
when i use this i get
result as:
total weight
NULL
1.7
Please help!!
May 10, 2012 at 5:26 am
This seems to be a very simple issue, but people are having difficulty because they don't have enough information to solve it
It would be better if you post some sample data along with the DDL
You will get answers quicker and properly tested
Please check the link given in my signature to know how to do it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 10, 2012 at 5:33 am
ranuganti (5/10/2012)
so how should we use this statement without returning 2 recordsselect
case when Void = 0 then SUM(weight) end AS totalweight
from test
when i use this i get
result as:
total weight
NULL
1.7
Please help!!
I don't see that anybody has suggested you this solution above :unsure:
Did you try what Joseph and Dwain suggested?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 10, 2012 at 5:41 am
This might work:
select
Count(case when void = 0 then count(ID) Else NULL end) as Countofsuccess,
Count(case when void = 1 then count (ID) Else NULL end) as countoffailure
from test
If you are looking for Sum then just replace "Count" with "Sum".
May 10, 2012 at 6:34 am
ranuganti (5/10/2012)
so how should we use this statement without returning 2 recordsselect
case when Void = 0 then SUM(weight) end AS totalweight
from test
when i use this i get
result as:
total weight
NULL
1.7
Please help!!
When you group by Void, you will get one row of output for every value of Void, including a row for nulls if there are any:
SELECT Void, Counts = COUNT(*)
FROM #Test
GROUP BY Void
-- results:
-- VoidCounts
-- NULL1
-- 01
-- 12
But the result can be aggregated into one row easily:
SELECT
Valid = SUM(CASE WHEN Void = 0 THEN Counts ELSE 0 END),
InValid = SUM(CASE WHEN Void = 1 THEN Counts ELSE 0 END)
FROM (
SELECT Void, Counts = COUNT(*)
FROM #Test
GROUP BY Void
) d
Results:
ValidInValid
1 2
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply