SQL Ninjas ...  Help polish this query

the_b1ackfox
the_b1ackfox used Ask the Experts™
on
Hello experts!  I have a query which is mocking me by hiding just outside my current abilities to understand how to get it to do what I want, (which for this 1 instance does not include world dominiation... for now).

Anyways, I have attached the code...

What I am trying to do, 1) generate a sum of the counts in the CT column ( I need the individual counts, but I need a sum too), and two see if it is possible to count multiple things in a query... so I don't have to run multiple queries to accomplish my (non-world conquest) goals.

To the warrior who accepts this mission and provides enlightenment, I will provide 500 points for now, and their own vacation island paradise named after them (you must suggest the name btw), once I have taken over the world.  Warriors who perform as a team, shall receive a portion of the 500 pts,  and their own island paradise (again after they have suggested a name)...  and they will automatically be entered into a raffle, to determine which one will have my mother in law, and my wifes dog live on their island as well...  :)  Ah... if only

Anyways, Thank you in advance for any and all efforts, I will try my best to learn better than a drunken chimpanzee.

Humbly,

--- me

And this doesn't involve reporting of any kind...but the solution could.. maybe...  I dunno
Select rd.nm_last , rd.userid, count(rd.userid) as Ct
From mabresults mb
inner join W_Ptproc PP on pp.pat_exam_id=mb.xm_id
inner join remote.dbo.w_rads  RD on RD.userid=pp.prov_id
Where mb.sts_cd = '10'  
and pp.proc_start_dttm between '2008-11-01' and '2009-10-31'
and mb.bio = '0' 
group by rd.nm_last, rd.userid order by rd.nm_last

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Commented:
sure you can use more than one aggregation function in a single select. look at the one  below
Select rd.nm_last , rd.userid, count(rd.userid) as Ct, SUM(rd.userid) AS sumOfUserid
From mabresults mb
	inner join W_Ptproc PP on pp.pat_exam_id=mb.xm_id
	inner join remote.dbo.w_rads  RD on RD.userid=pp.prov_id
Where mb.sts_cd = '10'  
  and pp.proc_start_dttm between '2008-11-01' and '2009-10-31'
  and mb.bio = '0' 
group by rd.nm_last, rd.userid 
order by rd.nm_last, rd.userid

Open in new window

HainKurtSr. System Analyst

Commented:
please post a sample result...
ralmada

Commented:
>>generate a sum of the counts in the CT column <<
So you mean the overall count? You can try using the OVER clause. See below:

Select 	rd.nm_last , 
	rd.userid, 
	count(rd.userid) over (partition by rd.nm_last, rd.userid) as Ct,
	count(rd.userid) over (partition by 0) as totalCT
From mabresults mb 
inner join W_Ptproc PP on pp.pat_exam_id=mb.xm_id 
inner join remote.dbo.w_rads  RD on RD.userid=pp.prov_id 
Where mb.sts_cd = '10'   
and pp.proc_start_dttm between '2008-11-01' and '2009-10-31' 
and mb.bio = '0'  
order by rd.nm_last

Open in new window

ralmada
Commented:
oops missed something
Select  distinct 
        rd.nm_last ,  
        rd.userid,  
        count(rd.userid) over (partition by rd.nm_last, rd.userid) as Ct, 
        count(rd.userid) over (partition by 0) as totalCT 
From mabresults mb  
inner join W_Ptproc PP on pp.pat_exam_id=mb.xm_id  
inner join remote.dbo.w_rads  RD on RD.userid=pp.prov_id  
Where mb.sts_cd = '10'    
and pp.proc_start_dttm between '2008-11-01' and '2009-10-31'  
and mb.bio = '0'   
order by rd.nm_last

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial