# Thread: Group By with Fix Row data

1. Registered User
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.

Regards
Somnath

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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?

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

Group By Col2 & Col3 and Sum Col4

Regards
Somnath

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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.

5. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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```

#### Posting Permissions

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