CODEWITHSUNDEEP
oracle-databaseselect
Barta Tamás
Barta Tamás

Reputation: 899

Oracle, get all columns

I got this statement:

select count(*), 
       article_no_external, 
       article_group_id
  from tbl_erp_article
 where article_no_external != ' '
 group by article_no_external, article_group_id
having count(*) >1

I want to group by group_id and external_no, this works just fine, I get 128 records. But I would like to see all columns not only those 2. I tried to add them to the select, but then I get an error with the group by. I need 4 more columns cause I need to grab them to make a new record using the selected data.

Upvotes: 0

Views: 141

Answers (2)

user330315
user330315

Reputation: 

select article_no_external, article_group_id, col2, col3, col4, col5
from (
  select article_no_external, article_group_id, col2, col3, col4, col,
         count(*) over (partition by article_no_external, article_group_id) as cnt
  from tbl_erp_article
  where article_no_external <> ' '
) 
where cnt > 1;

If you want to find non-empty varchar columns remember that Oracle doesn't have an empty string. An '' is converted to NULL during inserts or updates. So you probably want where article_no_external IS NOT NULL

Upvotes: 2

hkutluay
hkutluay

Reputation: 6944

You cant get all column values when you aggregate your fields for count, sum etc.

Not exacly same result but this may help you.

select * 
from tbl_erp_article
where  article_no_external != ' ' and
(article_no_external, article_group_id) in (
select article_no_external, article_group_id
from tbl_erp_article
where article_no_external != ' '
group by article_no_external, article_group_id
having count(*) >1)

Upvotes: 1

Related Questions