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

show us your query and we will help you

Jump to Post

just run an extra select statement from this query

select sum(quantity) 
from (
-- the above select)
)
Jump to Post

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 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.