SQL Sum result of Count

[LAMDB]

Hi all,

I need to run a query that performs a count(*) on a table. This gives me the subtotals for each group. Now I need to sumarize the subtotals with sum the results given by the previous count.

What would be the best way to do this?

Thanks.

[dickersonka]

show us your query and we will help you

[LAMDB]

show us your query and we will help you

select
CFGOpportunityType.description as Market,
opportunity.stage as Stage,
CFGOpportunityStage.description as Description,
count(*) as Quantity

from
opportunity, CFGOpportunityType, CFGOpportunityStage

where
opportunity.opportunityType = CFGOpportunityType.code and
CFGOpportunityType.description = 'Commercial' and
CFGOpportunityStage.code = opportunity.stage and
opportunity.createDate >= '2008-01-01'

group by
opportunity.stage,
CFGOpportunityStage.description,
CFGOpportunityType.description

The query above returns 4 columns: Market, Stage, Description, Quantity. I would now need to SUM() the QUantity Column.

Thanks for your help in advance!

LAM.

[dickersonka]

just run an extra select statement from this query

select sum(quantity) 
from (
-- the above select)
)

[LAMDB]

just run an extra select statement from this query

select sum(quantity) 
from (
-- the above select)
)

I tried this before posting the original question and did not work:

select sum(quantity) from (

select
CFGOpportunityType.description as Market,
opportunity.stage as Stage,
CFGOpportunityStage.description as Description,
count(*) as Quantity

from
opportunity, CFGOpportunityType, CFGOpportunityStage

where
opportunity.opportunityType = CFGOpportunityType.code and
CFGOpportunityType.description = 'Aviation' and
CFGOpportunityStage.code = opportunity.stage and
opportunity.createDate >= '2008-01-01'

group by
opportunity.stage,
CFGOpportunityStage.description,
CFGOpportunityType.description
)

I get this error:
Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near ')'.

[nahjipeel]

SELECT sum(quantity)
FROM (
-- the above select)
) as A