SQL Group problem

Question

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'

Answer 1

Try to add other columns from SELECT into GROUP BY: ID and WIFENAME

Answer 2

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"

Answer 3

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

Answer 4

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'