counting with different conditions

Hi, I am unable to find a solution for this which I initially thought
would be easy
TABLE tbl { n1 INTEGER, n2 INTEGER }
I need to count for the whole domain
1) n1 < 1000
2) n2 < 1000
3) n1 < n2
more conditions ...
I don't want to create a view per condition. It will make it slow for
my client/server environment.
Is it possible to create a view { count(condition1),
count(condition2), ... }
Help please
RiyazOn 16.05.2007 10:08, riyaz.mansoor@.gmail.com wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
>
Use CASE.
robert|||On May 16, 1:08 pm, "riyaz.mans...@.gmail.com"
<riyaz.mans...@.gmail.com> wrote:
> Hi, I am unable to find a solution for this which I initially thought
> would be easy
> TABLE tbl { n1 INTEGER, n2 INTEGER }
> I need to count for the whole domain
> 1) n1 < 1000
> 2) n2 < 1000
> 3) n1 < n2
> more conditions ...
> I don't want to create a view per condition. It will make it slow for
> my client/server environment.
> Is it possible to create a view { count(condition1),
> count(condition2), ... }
> Help please
> Riyaz
select (select count(*) from tbl where n1< 1000) as condition1 ,
(select count(*) from tbl where n2 < 1000) as condition2,
(select count(*) from t where n1 < n2) as condition3
Regards
Amish Shah
http://shahamishm.tripod.com|||Life Saver.

> select (select count(*) from tbl where n1< 1000) as condition1 ,
> (select count(*) from tbl where n2 < 1000) as condition2,
> (select count(*) from t where n1 < n2) as condition3
> Regards
> Amish Shahhttp://shahamishm.tripod.com|||On 16.05.2007 11:10, riyaz.mansoor@.gmail.com wrote:
> Life Saver.
[vbcol=seagreen]
Wait, 'till you see the performance of that. IMHO a solution involving
CASE is more efficient.
robert|||I'm just know basic SQL. Can u show me how to use CASE?

> Wait, 'till you see the performance of that. IMHO a solution involving
> CASE is more efficient.
> robert|||On 16.05.2007 12:24, riyaz.mansoor@.gmail.com wrote:[vbcol=seagreen]
> I'm just know basic SQL. Can u show me how to use CASE?
>
How about reading some documentation?
Hint: SUM(CASE ... END)
Cheers
robert
PS: Please do not top post.|||Below is an example of the CASE expression technique Riyaz mentioned. This
provides better performance than the subquery method in situations where the
entire table must be scanned because it's more efficient to scan once rather
than multiple times. However, with only sarable expressions (if you didn't
have n1 < n2), you could get better performance with the subqueries if n1
and n2 were both indexed.
SELECT
SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
FROM dbo.tbl
Hope this helps.
Dan Guzman
SQL Server MVP
<riyaz.mansoor@.gmail.com> wrote in message
news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
> I'm just know basic SQL. Can u show me how to use CASE?
>
>|||> Below is an example of the CASE expression technique Riyaz mentioned.
I meant to say "Robert mentioned". My apologies to both of you.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:52EBF1DE-98E5-4882-8FFF-DC18B098640D@.microsoft.com...
> Below is an example of the CASE expression technique Riyaz mentioned.
> This provides better performance than the subquery method in situations
> where the entire table must be scanned because it's more efficient to scan
> once rather than multiple times. However, with only sarable expressions
> (if you didn't have n1 < n2), you could get better performance with the
> subqueries if n1 and n2 were both indexed.
> SELECT
> SUM(CASE WHEN n1 < 1000 THEN 1 ELSE 0 END) AS n1_count,
> SUM(CASE WHEN n2 < 1000 THEN 1 ELSE 0 END) AS n2_count,
> SUM(CASE WHEN n1 < n2 THEN 1 ELSE 0 END) AS n1_less_than_n2_count
> FROM dbo.tbl
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <riyaz.mansoor@.gmail.com> wrote in message
> news:1179311071.285296.39540@.e65g2000hsc.googlegroups.com...
>