CODEWITHSUNDEEP
oracle-databasegroup-byoracle10g
Microsoft DN
Microsoft DN

Reputation: 10020

Group by subquery

I have following query

Select id, name, add1 || ' ' ||add2 address,
        case 
            when subId =1 then 'Maths'
            else 'Science'
        End,
        nvl(col1, col2) sampleCol
From Student_tbl
Where department = 'Student'

I want to group by this query by address as default

I tried

Group by add1 ,add2 ,id, name, subId, col1, col2

and 

Group by add1 || ' ' ||add2,id, name,
         case 
            when subId =1 then 'Maths'
            else 'Science'
        End,
        nvl(col1, col2)

Both group by returns same result. I am unsure which query is right. Anybody help me on this?

Upvotes: 0

Views: 76

Answers (3)

user5683823
user5683823

Reputation:

The two queries will not necessarily give the same result. Which is correct depends on your requirement. Here is an example, using just the new ADDRESS column which you get by aggregating the input columns ADD1 and ADD2.

Suppose in one row you have ADD1 = 123 Main Street, Portland, and ADD2 = Oregon. Then in the output, ADDRESS = 123 Main Street, Portland, Oregon

In another row you have ADD1 = 123 Main Street, and ADD2 = Portland, Oregon. For this row, the resulting ADDRESS is the same.

If you group by ADDRESS the two output rows will land in the same group, but if you group by ADD1, ADD2 they will be in different groups. In this example it is likely you want to group by ADDRESS, but in other, similar-structure cases that wouldn't be what you want or need.

Upvotes: 1

Thomas G
Thomas G

Reputation: 10216

After your last comment I THINK to finally understand what you are expecting from us.

Both queries are correct basically because for both approaches you've listed in your SELECT clause all the fields present in your GROUP BY clause

What you are doing is a bit strange here because the GROUP BY contains the id, I guess this is the unique identifier of each row so you are finally not grouping anything. You'll get as much as rows as your table contains.

The reason why it returns the same reults is purely data based. There might be scenarios where the 2 queries returns different results.

In your case, if it returns the same results, it would mean that col1 is never NULL

Upvotes: 1

StackUser
StackUser

Reputation: 5398

Always try to implement all the columns (with same format) that you mentioned in the select statement in "Group By" except aggregated columns. In your case I would prefer the second approach. 

SELECT id
    ,NAME
    ,add1 || ' ' || add2 address
    ,CASE 
        WHEN subId = 1
            THEN 'Maths'
        ELSE 'Science'
        END
    ,nvl(col1, col2) sampleCol
FROM Student_tbl
WHERE department = 'Student'
GROUP BY id
    ,NAME
    ,add1 || ' ' || add2
    ,CASE 
        WHEN subId = 1
            THEN 'Maths'
        ELSE 'Science'
        END
    ,nvl(col1, col2)

I can't see any aggregated columns in your Select. If your select does not require aggregation then you can simply get rid off group by. You can implement distinct in case of any duplicate records in your result set.

Upvotes: 3

Related Questions