--- In firebird-support@yahoogroups.com, luiz Rafael <culikr@...>
wrote:

>
> Dear Friends
>
> the query bellow is returning error for me, telling that the number

of

> columns dont match.
> as Far I know, only the colunms not specified on sum, should be on

group by

> query bellow
> CREATE VIEW rafaelzM (xcliente, xqtdr, xbruto, xliquido, xper,

impureza,

> umidade, ardido, danificado, colh, tran) AS SELECT
> gmitef."CLIENTE" AS "XCLIENTE",
> SUM(gmitef."QTDR") AS "XQTDR",
> SUM(gmitef."QUANTIDADE") AS "XBRUTO",
> gmitef."QTDR" AS "XLIQUIDO",
> ABS( (abs(gmitef."QUANTIDADE" - gmitef."QTDR") * 100) /
> gmitef."QUANTIDADE") AS "XPER",
> gmitef."QTDP1" AS "IMPUREZA",
> gmitef."QTDP2" AS "UMIDADE",
> gmitef."QTDP3" AS "ARDIDO",
> gmitef."QTDP4" AS "DANIFICADO",
> gmpedf."COLHEDEIR" AS "COLH",
> gmpedf."TRANSPORT" AS "TRAN"
> FROM
> "GMITEF" gmitef ,
> "GMPEDF" gmpedf WHERE gmitef."NOTA_FISCA" = gmpedf."NOTA_FISCA"

AND

> gmitef."TIPO_DOCTO" = gmpedf."TIPO_DOCTO" AND gmitef."FORNECEDOR" =
> gmpedf."FORNECEDOR" AND gmitef."QTDR" > 0 AND gmitef."MANUTENCAO"
>=
> '2006/01/01' AND gmitef."MANUTENCAO" <= '2006/12/31' AND

gmitef."SR_DELETED"

> = ' ' AND gmpedf."SR_DELETED" = ' '
> GROUP BY
> gmitef."CLIENTE",
> gmitef."QTDR",
> gmitef."QUANTIDADE",
> gmitef."QTDP1",
> gmitef."QTDP2",
> gmitef."QTDP3",
> gmitef."QTDP4",
> gmpedf."COLHEDEIR",
> gmpedf."TRANSPORT"
>
> Regards
> Luiz

Luiz,

Every expression in the select statement that is not an aggregate
(sum / count / etc), must be included in your group by clause.

You seem to be trying to group by gmitef."QUANTIDADE", however this
isn't in your select (directly anyway, it is used in the calculation
of another XPER)

You can substitute the field position for fields like this. In your
query, the fifth field is an expression, so simply substitute 5 in
its place in the group by:

CREATE VIEW rafaelzM (xcliente, xqtdr, xbruto, xliquido, xper,
impureza, umidade, ardido, danificado, colh, tran) AS SELECT
gmitef."CLIENTE" AS "XCLIENTE",
SUM(gmitef."QTDR") AS "XQTDR",
SUM(gmitef."QUANTIDADE") AS "XBRUTO",
gmitef."QTDR" AS "XLIQUIDO",
ABS( (abs(gmitef."QUANTIDADE" - gmitef."QTDR") * 100) /
gmitef."QUANTIDADE") AS "XPER",
gmitef."QTDP1" AS "IMPUREZA",
gmitef."QTDP2" AS "UMIDADE",
gmitef."QTDP3" AS "ARDIDO",
gmitef."QTDP4" AS "DANIFICADO",
gmpedf."COLHEDEIR" AS "COLH",
gmpedf."TRANSPORT" AS "TRAN"
FROM
"GMITEF" gmitef ,
"GMPEDF" gmpedf WHERE gmitef."NOTA_FISCA" = gmpedf."NOTA_FISCA" AND
gmitef."TIPO_DOCTO" = gmpedf."TIPO_DOCTO" AND gmitef."FORNECEDOR" =
gmpedf."FORNECEDOR" AND gmitef."QTDR" > 0 AND gmitef."MANUTENCAO" =
'2006/01/01' AND gmitef."MANUTENCAO" <= '2006/12/31' AND
gmitef."SR_DELETED"
= ' ' AND gmpedf."SR_DELETED" = ' '
GROUP BY
gmitef."CLIENTE",
gmitef."QTDR",
5,
gmitef."QTDP1",
gmitef."QTDP2",
gmitef."QTDP3",
gmitef."QTDP4",
gmpedf."COLHEDEIR",
gmpedf."TRANSPORT"



Adam