SQL script - sum total issue

[Jeffrey S via SQLMonster.com]

I have this script...I would like to group the total of project_amt_1 (July)
by the project_id and the same for the 11 months and the total.

select a.project_id,a.component_id,a.cost_element_id,a.pr oject_amt_1 as
'July',
a.project_amt_2 as 'August', a.project_amt_3 as 'September',a.project_amt_4
as 'October',
a.project_amt_5 as 'November',a.project_amt_6 as 'December', a.project_amt_7
as 'January',
a.project_amt_8 as 'February', a.project_amt_9 as 'March', a.project_amt_10
as 'April',
a.project_amt_11 as 'May', a.project_amt_12 as 'June', a.project_ytd_amt as
'2006 Total'
from component_monetary_balance as a, project as b, component as c
where a.project_id = b.project_id
and b.project_id = c.project_id
and a.project_id = c.project_id
and a.component_id = c.component_id
and a.amt_class_type = 'BOARDBUD'
and a.fiscal_year = '2006'
and a.curr_type = 'P'
and a.project_id between '01000' and '09999'
and not a.project_id in ('01067','01069','01070','01071','01072','01073',
'01074','01075','01076','01300')
and not a.component_id between '8000' and '8999'
and (not a.cost_element_id like '7639%'
or not a.cost_element_id like '8032%'
or not a.cost_element_id like '8887%'
or not a.cost_element_id like '8888%'
or not a.cost_element_id like '9685%'
or not a.cost_element_id like '9683%'
or not a.cost_element_id like '9681%'
or not a.cost_element_id like '9950%'
or not a.cost_element_id like '9955%'
or not a.cost_element_id like '8499%')
and b.project_status in ('T','A')
and c.component_status in ('T','A')
and project_ytd_amt <> 0

Thanks
--
Message posted via http://www.sqlmonster.com

[Erland Sommarskog]

Jeffrey S via SQLMonster.com (fo***@SQLMonster.com) writes:

I have this script...I would like to group the total of project_amt_1
(July) by the project_id and the same for the 11 months and the total.

It is unclear to me what you want. If all you want is the sum per
project and no information per component and cost_element, then
you can say:

select a.project_id, SUM(a.project_amt_1) as 'July',
SUM(a.project_amt_2) as 'August'
...
from component_monetary_balance as a, project as b, component as c
...
and project_ytd_amt <> 0
GROUP BY a.project_id

If this does not answer you question, could you please clarify with
an example?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Jeffrey S via SQLMonster.com]

Lightbulb...thanks for the help...toggling between SQL and Cognos I forgot
the most obvious...

Erland Sommarskog wrote:

I have this script...I would like to group the total of project_amt_1
(July) by the project_id and the same for the 11 months and the total.

It is unclear to me what you want. If all you want is the sum per
project and no information per component and cost_element, then
you can say:

select a.project_id, SUM(a.project_amt_1) as 'July',
SUM(a.project_amt_2) as 'August'
...
from component_monetary_balance as a, project as b, component as c
...
and project_ytd_amt <> 0
GROUP BY a.project_id

If this does not answer you question, could you please clarify with
an example?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

--
Message posted via http://www.sqlmonster.com