locked
How to create view with conditional grouping RRS feed

  • Question

  • Hi Guys,

    I am using MS-SQL2000.

    I want to create a view with conditional grouping for a report but I don't know how to do it.

    For example I have this table,

    Name   Type  Subtype   Amount

    A           1         C          100
    A           1         C          200
    A           1         D          500
    A           1         D          100
    B           2         C          110
    B           2         C          140


    If type=1 then sum(Amount) where Subtype=D group by Name
    else sum(Amount) group by Name

    The result I want should be

    Name   Amount
    A           600
    B           250


    Thanks

    Jessen

    Tuesday, July 12, 2011 10:00 AM

Answers

  • Hi, try :

    select [name],SUM(Case When (Type=1 and Subtype='D') Or Type<>1 Then Amout Else 0 END) as Amount
    From YourTable
    Group by [name]

     

     

    Best regards
    • Proposed as answer by RamJaddu Tuesday, July 12, 2011 10:08 AM
    • Marked as answer by Jessenchow Tuesday, July 12, 2011 12:19 PM
    Tuesday, July 12, 2011 10:05 AM

All replies

  • Hi, try :

    select [name],SUM(Case When (Type=1 and Subtype='D') Or Type<>1 Then Amout Else 0 END) as Amount
    From YourTable
    Group by [name]

     

     

    Best regards
    • Proposed as answer by RamJaddu Tuesday, July 12, 2011 10:08 AM
    • Marked as answer by Jessenchow Tuesday, July 12, 2011 12:19 PM
    Tuesday, July 12, 2011 10:05 AM
  • As you want results dynamic, you will have to provide values as parameters to your view. But with VIEWS you cannot add parameters.

    Try creating Multistatement Table Valued Function, link: http://msdn.microsoft.com/en-us/library/aa214363%28v=sql.80%29.aspx

     

    The function can be called with SELECT * from function_name (param1, param2, paramN) statement from anywhere.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Tuesday, July 12, 2011 10:11 AM
  • Small change in your code...

     

    select [name],SUM(Case When (Type=1 and Subtype='D') Or 1=1 Then Amout Else 0 END) as Amount
    From YourTable
    Group by [name]

    Pls mark as answer, if this solves your prob...

     

     

     


    - Kerobin
    • Proposed as answer by HildaHilda Tuesday, July 12, 2011 10:35 AM
    Tuesday, July 12, 2011 10:20 AM
  • I think @Jessen want it this way:

    SELECT NAME, SUM(Amount) as AMOUNT
    WHERE Type = @TypeFilter
    GROUP BY NAME
    

    ... but this can be done as SQL stmt only & can't achieved in a VIEW.

    Check my earlier response to create a MTV-UDF.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Tuesday, July 12, 2011 10:30 AM
  • Hi Guys,

    Thank you for all your responds and info.

     

    Regards

    Jessen

    Tuesday, July 12, 2011 12:18 PM