Complex Count-Group Relation

[digimortal]

Hi all,
I can not run this Query

SELECT SUM(I.QUANTITY), (SELECT DISTINCT COUNT(COLOR) FROM COLORTABLE WHERE I.MATERIAL = MATERIAL GROUP BY MATERIAL, REVISION) as COL
FROM SOLDITEMS I, COLOR C WHERE I.QUNIT = 'KG' AND I.CREATEDAT > '01.06.2005' AND I.CREATEDAT < '30.06.2005'
GROUP BY COL

COLORTABLE holds the colors used in a product in different records so I count them to get the number of colors used for an item.

This is what I'm trying to do:
We sold 100kg of items we use 2 color for
We sold 233kg of items we use 7 color for
etc...

I hope someone helps 'cause I'm in big trouble here...

 

[micha123]

I see a problem here: you probably have a Cartesian product for the join with the COLOR table, because it is not used and there are no conditions on it (is this what you want...?)
Another problem is that I think you can't do a GROUP BY on columns that do not appear in the select list.
And a question: can this command be divided into 2 commands including a temporary table, or do you need it to be only one select?
M.