Group By with Fix Row data

[Somnath_IT2006]

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 By Col2 and 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

[recyan]

Not the solution, but a question :
Is it possible that your tables could also have more than 2 distinct values for Col3 (i.e. other than "E" & "C")& hence Col4 (eg Col1 Nos 6,7,8)
Col1--Col2--Col3--Col4
1-----ABC--E-----5000
2-----ABC--C-----6000
3-----ABC--E-----4000
4-----XYZ--E-----3000
5-----PQR--C-----5000


6-----ABC--P-----3000
7-----ABC--Q-----1500
8-----ABC--R-----2500

Thanks

[Somnath_IT2006]

No there will be only two values in Col3 and that is fix "C" or "E"...

Thanks for reply.

[recyan]

Just check out if below gives some guidelines :

The table :
mytable
Col1
Col2
Col3
Col4



The sub-queries :

qryDistinctCol2

SELECT DISTINCT 
	myTable.Col2
FROM 
	myTable
ORDER BY 
	myTable.Col2;

qryDistinctCol3

SELECT DISTINCT 
	myTable.Col3
FROM 
	myTable
ORDER BY 
	myTable.Col3;

qryMinCol1SumCol4

SELECT 
	Min(myTable.Col1) AS MinOfCol1, 
	myTable.Col2, 
	myTable.Col3, 
	Sum(myTable.Col4) AS SumOfCol4
FROM 
	myTable
GROUP BY 
	myTable.Col2, myTable.Col3
ORDER BY 
	myTable.Col2, myTable.Col3;

qryDistinctCol2Col3

SELECT 
	qryDistinctCol2.Col2, 
	qryDistinctCol3.Col3
FROM 
	qryDistinctCol2, 
	qryDistinctCol3
ORDER BY 
	qryDistinctCol2.Col2, qryDistinctCol3.Col3;

qryMinColNoForSingleCol3

SELECT 
	Min(qryMinCol1SumCol4.MinOfCol1) AS MinOfMinOfCol1, 
	qryMinCol1SumCol4.Col2
FROM 
	qryMinCol1SumCol4
GROUP BY 
	qryMinCol1SumCol4.Col2
HAVING 
	(((Count(qryMinCol1SumCol4.Col3))<2));

qryAllCol1Col2MinCol1SunCol4

SELECT 
	qryDistinctCol2Col3.Col2, 
	qryDistinctCol2Col3.Col3, 
	qryMinCol1SumCol4.MinOfCol1, 
	NZ([SumOfCol4],0) AS MaxSumOfCol4
FROM 
	qryDistinctCol2Col3 
	LEFT JOIN 
	qryMinCol1SumCol4 
	ON 
	(qryDistinctCol2Col3.Col2 = qryMinCol1SumCol4.Col2) AND (qryDistinctCol2Col3.Col3 = qryMinCol1SumCol4.Col3)
ORDER BY 
	qryDistinctCol2Col3.Col2, qryDistinctCol2Col3.Col3;

The final query to be run :

qryFinal

SELECT 
	NZ([MinOfCol1],[MinOfMinOfCol1]) AS Col1No, 
	qryAllCol1Col2MinCol1SunCol4.Col2, 
	qryAllCol1Col2MinCol1SunCol4.Col3, 
	qryAllCol1Col2MinCol1SunCol4.MaxSumOfCol4, 
	qryMinColNoForSingleCol3.Col2, 
	qryMinColNoForSingleCol3.MinOfMinOfCol1
FROM 
	qryAllCol1Col2MinCol1SunCol4 
	LEFT JOIN 
	qryMinColNoForSingleCol3 
	ON 
	qryAllCol1Col2MinCol1SunCol4.Col2 = qryMinColNoForSingleCol3.Col2
ORDER BY 
	qryAllCol1Col2MinCol1SunCol4.Col2, qryAllCol1Col2MinCol1SunCol4.Col3;

Perhaps if we had another table (with distinct Col2 & Col3 values), things could have been easier:
tblCol2Col3

ID | Col2 | Col3
1 | ABC | C
2 | ABC | E
3 | PQR | C
4 | PQR | E
5 | XYZ | C
6 | XYZ | E 

Thanks