SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Making a view with summarized data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Naven
Starting Member

South Africa
2 Posts

Posted - 05/06/2013 :  03:17:05  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/06/2013 :  04:31:08  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Naven
Starting Member

South Africa
2 Posts

Posted - 05/06/2013 :  05:18:33  Show Profile  Reply with Quote
A great solution. Thank you.

Naven
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/06/2013 :  06:18:40  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000