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

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

Show quoteHide quote

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

Show quoteHide quote

hi

i am sorry, you can try this:

select count(*) from tbl1, tbl2 where tbl1.f1=tbl2.f2


Show quoteHide quote

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

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/gmgxsl.dll/htmlkb?usexsl=y&sp=psearch_article_text&@sa_id=58


Show quoteHide quote

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

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=t_dep­t.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

I really appraciate your help.

Ouyang

I'd suggest putting your query in a procedure like this:

create proc dbo.Departments
  (
  @rowCount   int   = null   output
  )
as
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_dep­t.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

set @rowCount = @@rowcount
go


And you call this procedure like this:

declare @rowCount int
set @rowCount = 0
exec dbo.Departments @rowCount = @rowCount output
print @rowCount

Of course instead of printing the value of the rowCount parameter, you
should map it to a variable in your code.


ML

The procedure works. But python coding might be complicated for my
application. I am still checking the possiblity to use a simple DDL to
do that. I followed the example Guillaume listed 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_dep­­t.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
COMPUTE COUNT(t_all_status.enter_diag)

The COMPUTE COUNT(t_all_status.enter_diag) associated with the original
DDL does give me the value of the total row too. But the returned data
from SQL server are two sets: one set is from the original DDL and the
other set is from COMPUTE COUNT (....).  Looks like simpler than stored
procecedure. This also bring me some python coding problem for the
application.
  Do you have any other idea to get the total row with a single DDL and
one set of data returned by SQL server?

  Ouyang

Are you trying to tell me that Python cannot read output parameters from
queries?

And it cannot receive more than one dataset from one database connection?

If either one of the above is true, you should seriously think about
changing your development tools. :)

I think the rest of your questions can be answered in a python programming
newsgroup.


ML

Thank you for your help, ML. I am working on python coding and see what
I do with it.

Is there a smart set-based solution?
My table design - where did I go wrong?
Alter table / update... set - strange problem
Newbee question about permissions and sp_OACreate
Can you have one field containing multiple data?