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.

Recommended Answers

All 5 Replies

show us your query and we will help you

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.

just run an extra select statement from this query

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

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 ')'.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.