Actually, I would avoid sub selects in the select clause. Unless your data
set is small, it would be less costly to join upon your counts.
select
s.someID
, sum(s.colA)
, sum(s.colB)
, sq1.someAlias
from
someTable t
join
(
select
someID
, count(*) 'someAlias'
from
someTable
) sq1
on s.someID = sq1.someID
group by
s.someID
The from clause query is costs less versus the select clause sub-select is
which increases cost with each row.
Teddy
On 2/9/07, Ray Thompson <[EMAIL PROTECTED]> wrote:
>
> Use subselects in the query to get the counts of all the records.
>
> Select sum(column1) as Sum1,
> Sum(column2) as sum2,
> (select count(*)
> From table
> Where somecondition) as TotalCount
> >From table
> Where anothercondition
>
> Ray Thompson
> Tau Beta Pi (www.tbp.org)
> The Engineering Honor Society
> 865-546-4578
>
> -----Original Message-----
> From: Michele Michele [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 08, 2007 10:22 AM
> To: SQL
> Subject: using count on one column
>
> OK, this is probably going to sound dumb, but I need to get a count on one
> column in a long SQL statement where the rest of the columns are using
> sum.
> My record count is only counting based on my search criteria, but I need a
> count on the number of detail records form where it is getting that
> information. Does that make any sense?
>
> Thanks for any help, I have so much to do I can't think!
>
> Thanks,
> Michele
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2725
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
