Making a view with summarized data

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 2008 Forums

Transact-SQL (2008)

Making a view with summarized data

Author

Topic

Naven
Starting Member

2 Posts

Posted - 2013-05-06 : 03:17:05

I hope I capture the full nature of my issue.

Say I have a table: Name, Surname, Type, Num1, Num2, Num3, as fields

I want to produce a view like: Name, Surname, Sum1, Sum2

Where Sum1 = Num1 + Num2 if Type = 'A' and Sum2 = Num2 + Num3 if Type = 'B'

For eg. in table if I have:

Naven Naidoo A 10 11 12

Naven Naidoo B 10 11 12, as two records (only different in type).

Then in view I would like:

Naven Naidoo 21 23, as one row.

How can I build a view like that? When I do it I get two rows instead of one. Like:

Naven Naidoo 21 0

Naven Naidoo 0 23

Naven

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 04:31:08

[code]
CREATE VIEW YourViewName
AS
SELECT Name,Surname,
SUM(CASE WHEN Type='A' THEN Num1 + Num2 ELSE 0 END) AS Sum1,
SUM(CASE WHEN Type='B' THEN Num2 + Num3 ELSE 0 END) AS Sum2
FROM Table
GROUP BY Name,Surname
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Naven
Starting Member

2 Posts

Posted - 2013-05-06 : 05:18:33

A great solution. Thank you.

Naven

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-06 : 06:18:40

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources