Totalling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Totalling

Author

Topic

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-02-16 : 09:23:03

I wrote a query that bring me back the following results. I want to know if it is possile to include in the query another column that will give me a grand total for each year. I need to also calculate % of group 1, 2, 3 of te total for any given year. Is this possible to do in a query?

YEAR GROUP TOTAL
===== ===== =====
2000 1 20
2000 2 40
2000 3 10
2001 1 80
2001 2 155
2001 3 1000
2002 1 10000
2002 2 8000
2002 3 12000

Here is the query I am using to give me the above result.

SELECT DISTINCT file_year, a.[group],
CASE
WHEN a.[GROUP]in ('1','2','3') THEN count(b.files)
END AS total
FROM
GROUP_LOOKUP_VW A
JOIN ANSWER D ON A.PROGRAM=D.PROGRAM AND A.QUES_VER=D.QUES_VER AND A.QUESTION=D.QUESTION AND A.ANSWER=D.ANSWER
JOIN SUMMARY B ON D.PROGRAM=B.PROGRAM AND D.QUES_VER=B.PROOF_VERSION AND Q2=D.ANSWER
JOIN DBO.GROUP_VW C ON A.[GROUP]=C.[GROUP]
group by file_year,a.[group]
order by file_year, a.[group]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-16 : 09:40:44

[code]
declare @table table
(
[YEAR] int,
[GROUP] int,
[TOTAL] int
)
insert into @table
select 2000, 1, 20 union all
select 2000, 2, 40 union all
select 2000, 3, 10 union all
select 2001, 1, 80 union all
select 2001, 2, 155 union all
select 2001, 3, 1000 union all
select 2002, 1, 10000 union all
select 2002, 2, 8000 union all
select 2002, 3, 12000

select t.*, g.grant_total, percentage = round([TOTAL] * 100.0 / grant_total, 2)
from @table t
inner join
(
select [YEAR], grant_total = sum(TOTAL)
from @table
group by [YEAR]
) g
on t.[YEAR] = g.[YEAR]

/*
YEAR GROUP TOTAL grant_total percentage
----------- ----------- ----------- ----------- ----------------------------
2000 1 20 70 28.570000000000
2000 2 40 70 57.140000000000
2000 3 10 70 14.290000000000
2001 1 80 1235 6.480000000000
2001 2 155 1235 12.550000000000
2001 3 1000 1235 80.970000000000
2002 1 10000 30000 33.330000000000
2002 2 8000 30000 26.670000000000
2002 3 12000 30000 40.000000000000
*/
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 09:46:08

[code]-- prepare sample data
declare @t table (year smallint, [group] tinyint, total smallint)

insert @t
select 2000, 1, 20 union all
select 2000, 2, 40 union all
select 2000, 3, 10 union all
select 2001, 1, 80 union all
select 2001, 2, 155 union all
select 2001, 3, 1000 union all
select 2002, 1, 10000 union all
select 2002, 2, 8000 union all
select 2002, 3, 12000

-- show the result
select t.year,
t.[group],
t.total,
1.0 * t.total / x.s as pct
from @t as t
inner join (
select year,
sum(total) as s
from @t
group by year
) as x on x.year = t.year[/code]

Peter Larsson
Helsingborg, Sweden

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-02-16 : 09:50:54

Thanks. That works wonderfully.

mdhingra01
Posting Yak Master

179 Posts

Posted - 2007-02-16 : 14:12:28

The answers you gave for total and % is perfect. They work. However, my insert query needs some modification and not really sure how to do this. Right now in my join ON TABLE B I am equating the following B.Q2=D.ANSWER when d.question_id='2'. B has another column that I need to evaluate aswell for this query and that is b.Q5=D.ANSWER when QUESTION_ID='5'. This second restriction only ever occurs when program='DA''. Therefore if the Program='DA' I need to evaluate the b.Q5=D.ANSWER, and if program<>'DA' then evaluate B.Q2=D.ANSWER.

THIS WAS MY ATTEMPT AT IT:
declare @table table
(
[YEAR] int,
[GROUP] int,
[GROUP_NAME] varchar (50),
[TOTAL] int
)
insert into @table
SELECT DISTINCT file_year,a.[group], c.cd_nm_en,
CASE
WHEN a.[GROUP]in ('1','2','3') THEN count(b.file_occurence_no)
END AS total
FROM
GROUP_LOOKUP_VW A
JOIN prf_ANSWER D ON A.PROGRAM_CD=D.PROGRAM_CD AND A.QUES_VER=D.QUES_VER AND A.QUESTION_ID=D.QUESTION_ID AND A.ANSWER_CD=D.ANSWER_CD
JOIN prf_SUMMARY B ON D.PROGRAM_CD=B.PROGRAM_CD AND D.QUES_VER=B.PROOF_VERSION AND (Q2A=D.ANSWER_CD AND D.QUESTION_ID='2' AND B.PROGRAM_CD <>'DA') OR (Q5A=D.ANSWER_CD AND D.QUESTION_ID='5' AND B.PROGRAM_CD='DA')
JOIN DBO.GROUP_VW C ON A.[GROUP]=C.[GROUP]
group by file_year,c.cd_nm_en,a.[group]
order by file_year,a.[group],c.cd_nm_en

select t.*, g.grand_total, percentage = round([TOTAL] * 100.0 / grand_total, 2)
from @table t
inner join
(
select [YEAR], grand_total = sum(TOTAL)
from @table
group by [YEAR]
) g
on t.[YEAR] = g.[YEAR]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-16 : 14:34:57

Please provide some proper sample data for all cases that can occur, and your expected output based on the sample data.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources