Group By with Fix Row data

Somnath_IT2006 · #1 (**permalink**) 01-12-12, 04:22

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

gvee · #2 (**permalink**) 01-12-12, 06:40

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?

Somnath_IT2006 · #3 (**permalink**) 01-12-12, 07:04

Oh... Sorry...

Group By Col2 & Col3 and Sum Col4

Thanks for reply

Regards
Somnath

gvee · #4 (**permalink**) 01-12-12, 08:43

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.

Somnath_IT2006 · #5 (**permalink**) 01-12-12, 08:49

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

gvee · #6 (**permalink**) 01-12-12, 09:13

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

Internet Services

Web Development

Digital Marketing

Consumer Tech