none
Creating a view with calculated and summarised data

    Frage

  • 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

    Montag, 6. Mai 2013 07:04

Antworten

  • Hi,

    Check this:

    SELECT NAME, SURNAME,
    	CASE WHEN [TYPE] = 'A' THEN NUM1 + NUM2
    	ELSE NULL
    	END AS SUM1,
    	CASE WHEN [TYPE] = 'B' THEN NUM2 + NUM3
    	ELSE NULL
    	END AS SUM2
    FROM TABLENAME
    	

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MCSE Data Platform
    MCITP: SQL Server 2008 Administration/Development
    MCSA SQL Server 2012
    MCTS: SQL Server Administration/Development

    MyBlog

    • Als Antwort markiert Inarius01 Donnerstag, 6. Juni 2013 12:09
    Montag, 6. Mai 2013 11:25

Alle Antworten

  • Please include concise and complete samples in future posts. This includes table DDL and sample data DML. Do you really want two aggregate columns or one?

    DECLARE @Sample TABLE
        (
          [Name] NVARCHAR(255) ,
          Surname NVARCHAR(255) ,
          [Type] NCHAR(1) ,
          Num1 INT ,
          Num2 INT ,
          Num3 INT
        );
    
    INSERT  INTO @Sample
    VALUES  ( '1', '1', 'A', 1, 2, 3 ),
            ( '2', '2', 'B', 4, 5, 6 );
    
    SELECT  [Name] ,
            Surname ,
            CASE WHEN [Type] = 'A' THEN Num1 + Num2
                 ELSE NULL
            END AS Sum1 ,
            CASE WHEN [Type] = 'B' THEN Num2 + Num3
                 ELSE NULL
            END AS Sum2 ,
            CASE WHEN [Type] = 'A' THEN Num1 + Num2
                 WHEN [Type] = 'B' THEN Num2 + Num3
                 ELSE NULL
            END AS Sum3
    FROM    @Sample;

    Montag, 6. Mai 2013 07:50
  • Maybe my understanding of the problem is wrong but I'd like to aggregate 2/3 columns, depending on type, into 1/2 columns in View.

    Say we have: INSERT INTO @Sample  VALUES ( '1', '1', 'A', '1', '2', '3'), ( '1', '1', 'B', '1', '2', '3'); - only Type is different.

    Which means @Sample (two rows) looks like:

     1 1 A 1 2 3

     1 1 B 1 2 3.

    I want to produce: 1 1 3 5 (Name, Surname, Sum1 = Num1 + Num2 (for Type A), Sum2 = Num2 + Num3 (For Type B), ie Sum2 will be NULL if there was only one row (just 1 1 A 1 2 3) in @Sample. Basically I want one row in View for every distinct Name + Surname in @Sample.

    Does that make sense?

    Montag, 6. Mai 2013 09:09
  • Oh, I can do this and it works for this example.

    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

    But my query is alot more complicated. So let me go try and apply this and I will respond with the outcome.

    Thanks.

    Montag, 6. Mai 2013 09:21
  • Hi,

    Check this:

    SELECT NAME, SURNAME,
    	CASE WHEN [TYPE] = 'A' THEN NUM1 + NUM2
    	ELSE NULL
    	END AS SUM1,
    	CASE WHEN [TYPE] = 'B' THEN NUM2 + NUM3
    	ELSE NULL
    	END AS SUM2
    FROM TABLENAME
    	

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MCSE Data Platform
    MCITP: SQL Server 2008 Administration/Development
    MCSA SQL Server 2012
    MCTS: SQL Server Administration/Development

    MyBlog

    • Als Antwort markiert Inarius01 Donnerstag, 6. Juni 2013 12:09
    Montag, 6. Mai 2013 11:25