CODEWITHSUNDEEP
sqloracle-databasegroup-by
Microsoft DN
Microsoft DN

Reputation: 10030

group by returns extra row

I have a query like below

select name,
          sum(...) col1, 
          sum(...) col2,
          sum(...) col3
From 
          table1
group by name;

This query is returning 3 rows

Now suppose I want one more column say department in above query, I modified it as follows. Now the problem is, it is returning 4 rows(One extra row) which I do not want.

Now able to get the logic behind this and whether is it possible to get same result which I had without group by department and still getting its values. Please help.

select name, department
          sum(...) col1, 
          sum(...) col2,
          sum(...) col3
From 
          table1
group by name, department;

Upvotes: 0

Views: 219

Answers (3)

Maulik Shah
Maulik Shah

Reputation: 402

Use below query:

select name,
       listagg(department, ', ') within group (order by name),
       sum(...) col1, 
       sum(...) col2,
       sum(...) col3
  from table1
  group by name;

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14858

You could use list_agg() to concatenate departments: 

select name, sum(x1) col1, sum(x2) col2, sum(x3) col3,
       listagg(department,', ') within group (order by department) departments
  from table1 group by name order by name;

SQLFiddle

To show only distinct departments for each name aggregate data at first:

select name, sum(col1) col1, sum(col2) col2, sum(col3) col3,
       listagg(department,', ') within group (order by department) departments
  from (
    select name, department, sum(x1) col1, sum(x2) col2, sum(x3) col3
      from table1 group by name, department)
  group by name order by name;

Upvotes: 2

Luaan
Luaan

Reputation: 63772

The problem is that for a given name, there is more then one department. There's no way to aggregate the results to get around this - at best, you'd need to have multiple values of department in the resultset row's column. This is always tricky. On MS SQL, you could do something like (select department from table1 t where t.name = table1.name for xml raw), but I'm not sure if there's something that could be used like this in Oracle. Maybe a custom aggregate function?

If you don't strictly care about this, doing something like max(department) instead of grouping by it might work. But do understand the results you are getting - it might be entirely wrong.

Upvotes: 1

Related Questions