Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7

    Unanswered: Group By with Fix Row data

    Dear All


    I have table as like below:

    Col1--Col2--Col3--Col4
    1-----ABC--E-----5000
    2-----ABC--C-----6000
    3-----ABC--E-----4000
    4-----XYZ--E-----3000
    5-----PQR--C-----5000


    I want result something like:

    Col1--Col2--Col3--Col4
    1-----ABC--E-----9000
    2-----ABC--C-----6000
    4-----XYZ--E-----3000
    4-----XYZ--C-----0000
    5-----PQR--E-----0000
    5-----PQR--C-----5000


    - I want to Group the Col3 and sum Col4 accordingly.
    - If there is no row found with "E" or "C" then that should be created with NULL or ZERO value.


    Actually its very urgent, please let me know if somebody have any idea about this.

    Appreciate all your help.

    Regards
    Somnath

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't understand the expected results. Can you double check these are correct please?

    Groupd by column 3 and sum column 4 would give this:

    Col3--Col4
    E-----12000 (5000 + 4000 + 3000)
    C-----11000 (6000 + 5000)

    Right?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7
    Oh... Sorry...

    Group By Col2 & Col3 and Sum Col4

    Thanks for reply

    Regards
    Somnath

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So results like this...

    Col2--Col3--Col4
    ABC--E-----5000 + 4000 = 9000
    ABC--C-----6000
    XYZ--E-----3000
    PQR--C-----5000

    Right?

    You need to clarify your question.
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2011
    Location
    Pune, India
    Posts
    7
    Yes you are correct Sir, but my second statement says that:

    - If there is no row found with "E" or "C" then that should be created with NULL or ZERO value.

    So that's why, after getting below result:

    Col2--Col3--Col4
    ABC--E-----5000 + 4000 = 9000
    ABC--C-----6000
    XYZ--E-----3000
    PQR--C-----5000


    I wanted to add new rows with NULL values in Col4:

    Col1--Col2--Col3--Col4
    4-----XYZ--C-----0000
    5-----PQR--E-----0000


    Hope you got it..


    Regards
    Somnath

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT z.col2
         , z.col3
         , Sum(your_table.col4) As total
    FROM   (
            SELECT x.col2
                 , y.col3
            FROM   (
                    SELECT DISTINCT
                           col2
                    FROM   your_table
                   ) As x
                 , (
                    SELECT DISTINCT
                           col3
                    FROM   your_table
                   ) As y
           ) As z
     LEFT
      JOIN your_table
        ON your_table.col2 = z.col2
       AND your_table.col3 = z.col3
    GROUP
        BY z.col2
         , z.col3
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •