how to create index (Case when)

How to create index when there is a SQL statement like
Select count(1) as [Total],
sum(Case when Field1 < Field2 then 1 else 0 End) as [Selected]
from Table1
Thx in Adv
XLDBUse one index on each column ( field 1 field 2 ) ...use nonclustered for each

however, if table is small ( say less than 1000 row s ) SQL probably wont use indexes..it will scan whole table.|||the table has 2 million records|||actually, there are 2 similar tables that i use the query on...

the table with 2 million records is taking 57 seconds
the table with 40,000 records is taking 1 second

there is only 1 index on "Field1" in "Case When" statement

how to count the total rows for a query with group by?

Hi,
I have a query like
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
for example, I can get the results like
f1 f2 YY
--
x1 y1 2
x2 y2 5
x3 y3 1
As you can see, total rows for the query is 3 (rows). How can I get
the "3" in the one select query like:
f1 f2 YY Total_Rows
--
x1 y1 2 3
x2 y2 5 3
x3 y3 1 3
or write another query to count the total rows (to get the "3") only?
Thanks a lot.
Ouyanghi
just see this:
select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY ( select count(*) from tbl1,
tbl2 where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2 ) as total_rows from tbl1, tbl2
where
tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
probably this solves your problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||You may also want to summarise the data in a report style query using
COMPUTE, the link below will take you to an example on how to implement
this:
http://www.mssql.com.au/kb/html/gmg... />
&@.sa_id=58
"zxo102" <zxo102@.gmail.com> wrote in message
news:1122690598.655507.63480@.g47g2000cwa.googlegroups.com...
> Hi,
> I have a query like
> select tbl1.f1, tbl2.f2, count(tbl1.f1) as YY from tbl1, tbl2 where
> tbl1.f1=tbl2.f2 group by tbl1.f1, tbl2.f2
> for example, I can get the results like
>
> f1 f2 YY
> --
> x1 y1 2
> x2 y2 5
> x3 y3 1
>
> As you can see, total rows for the query is 3 (rows). How can I get
> the "3" in the one select query like:
> f1 f2 YY Total_Rows
> --
> x1 y1 2 3
> x2 y2 5 3
> x3 y3 1 3
> or write another query to count the total rows (to get the "3") only?
>
> Thanks a lot.
> Ouyang
>|||Hi Chandra,
this sql query:
"select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
tbl1.f1, tbl2.f2"
gives group count instead of total count from the query with "group
by".
Any ideas?
Thanks.
Ouyang|||hi
can you please send the ddl so that i can help u out
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||hi
i am sorry, you can try this:
select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"zxo102" wrote:

> Hi Chandra,
> this sql query:
> "select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2 group by
> tbl1.f1, tbl2.f2"
> gives group count instead of total count from the query with "group
> by".
> Any ideas?
> Thanks.
> Ouyang
>|||Hi Guillaume,
Thanks for your information. I got the total row using "compute".
But the returned results from SQL server are in two parts. The value
of the total row is in the second part.
Since I call this query from python application which can not grab the
value from the second part, do you know how to get the total row in a
query which just return the total row only?
Thanks a lot.
Ouyang|||Hi Chandra,
My ddl is as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=t_dept.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag = t_icd.icdcode)
group by t_dept.dept_name,t_all_status.enter_diag, t_icd.chinese_diag
order by r_SUM2 DESC
Thanks
Ouyang|||Put the row count in an output variable. Maybe something like this:
select *
from ...
set @.finalRowCount = @.@.rowcount
@.finalRowCount must be declared as an in/out parameter of your procedure.
ML|||Hi ML,
I don't know very much about the stuff you mentioned, can you give
me an exmaple for that using my DDL as follows:
select t_all_status.enter_diag AS r_ICDCODE,
count(t_all_status.enter_diag) AS r_SUM2,
t_icd.chinese_diag AS r_CHINESE,
t_dept.dept_name as r_DEPT_NAME
from t_all_status, t_icd,t_dept
where (t_all_status.enter_dept=3Dt_dep=ADt.dept_code)
AND (t_all_status.enter_diag not like 'M%')
AND (t_all_status.enter_diag =3D t_icd.icdcode)
group by t_dept.dept_name,t_all_status.=ADenter_diag,
t_icd.chinese_diag
order by r_SUM2 DESC=20
I really appraciate your help.
Ouyang