SQL Group problem
I have this query which is showing the ORA-00979:not a GROUP BY expression
error.
I'm not a sql pro so please could you give me a hint what's wrong ?
SELECT *
FROM (SELECT a.*, ROWNUM r___
FROM (SELECT DISTINCT "SMTAPP"."XF_MASTER"."ID" AS "XfMasterEntityId",
"SMTAPP"."XP_MASTER_DETAIL"."WIFENAME" AS "XpMasterDetailEntityWifename",
"SMTAPP"."XP_MASTER_DETAIL"."COUNT" AS "XpMasterDetailEntityCount",
MAX ("SMTAPP"."XP_MASTER_DETAIL"."COUNT") AS "XpMasterDetailEntityCountMax"
FROM ("SMTAPP"."XF_MASTER" LEFT JOIN "SMTAPP"."XP_MASTER_DETAIL"
ON "SMTAPP"."XF_MASTER"."ID" = "SMTAPP"."XP_MASTER_DETAIL"."XF_MASTER_ID")
GROUP BY "SMTAPP"."XP_MASTER_DETAIL"."COUNT"
开发者_运维百科 ORDER BY "SMTAPP"."XF_MASTER"."ID" ASC) a
WHERE ROWNUM < '11')
WHERE r___ >= '1'
When you use GROUP BY
, all columns that you select must either be aggregated using an aggregate function or be in the GROUP BY
clause. Otherwise you would have a list of items for that column, which is not a single value.
Therefore try this (replace the existing GROUP BY
with this one):
GROUP BY "SMTAPP"."XF_MASTER"."ID",
"SMTAPP"."XP_MASTER_DETAIL"."WIFENAME",
"SMTAPP"."XP_MASTER_DETAIL"."COUNT"
Try to add other columns from SELECT
into GROUP BY
: ID
and WIFENAME
In the inner most Query
SELECT DISTINCT
"SMTAPP"."XF_MASTER"."ID" AS "XfMasterEntityId",
"SMTAPP"."XP_MASTER_DETAIL"."WIFENAME" AS "XpMasterDetailEntityWifename",
"SMTAPP"."XP_MASTER_DETAIL"."COUNT" AS "XpMasterDetailEntityCount",
MAX ("SMTAPP"."XP_MASTER_DETAIL"."COUNT") AS "XpMasterDetailEntityCountMax"
FROM ("SMTAPP"."XF_MASTER" LEFT JOIN "SMTAPP"."XP_MASTER_DETAIL"
ON "SMTAPP"."XF_MASTER"."ID" = "SMTAPP"."XP_MASTER_DETAIL"."XF_MASTER_ID")
GROUP BY "SMTAPP"."XP_MASTER_DETAIL"."COUNT"
ORDER BY "SMTAPP"."XF_MASTER"."ID" ASC
you have columns in the SELECT i.e.
"SMTAPP"."XF_MASTER"."ID" AS "XfMasterEntityId",
AND
"SMTAPP"."XP_MASTER_DETAIL"."WIFENAME" AS "XpMasterDetailEntityWifename"
which are NOT part of the GROUP BY "SMTAPP"."XP_MASTER_DETAIL"."COUNT"
and that is not allowed.
You can only have columns in the SELECT that are part of the Group By or are aggregated using the Aggregate Functions like MAX / AVG etc
What @eumiro meant was
SELECT *
FROM (SELECT a.*, ROWNUM r___
FROM (SELECT DISTINCT "SMTAPP"."XF_MASTER"."ID" AS "XfMasterEntityId",
"SMTAPP"."XP_MASTER_DETAIL"."WIFENAME" AS "XpMasterDetailEntityWifename",
"SMTAPP"."XP_MASTER_DETAIL"."COUNT" AS "XpMasterDetailEntityCount",
MAX ("SMTAPP"."XP_MASTER_DETAIL"."COUNT") AS "XpMasterDetailEntityCountMax"
FROM ("SMTAPP"."XF_MASTER" LEFT JOIN "SMTAPP"."XP_MASTER_DETAIL"
ON "SMTAPP"."XF_MASTER"."ID" = "SMTAPP"."XP_MASTER_DETAIL"."XF_MASTER_ID")
GROUP BY "SMTAPP"."XF_MASTER"."ID", "SMTAPP"."XP_MASTER_DETAIL"."WIFENAME", "SMTAPP"."XP_MASTER_DETAIL"."COUNT"
ORDER BY "SMTAPP"."XF_MASTER"."ID" ASC) a
WHERE ROWNUM < '11')
WHERE r___ >= '1'
精彩评论