This discussion is archived
5 Replies Latest reply: Jan 4, 2013 3:23 AM by 852469 RSS

QUERY

852469 Newbie
Currently Being Moderated
create table inv_supplier(supp varchar2(30),com varchar2(30),cnt number)

insert into inv_supplier values('A','OIL1',1);
insert into inv_supplier values('A','OIL2',1);
insert into inv_supplier values('A','OIL3',1);
insert into inv_supplier values('A','OIL4',1);
insert into inv_supplier values('B','OIL3',1);
insert into inv_supplier values('B','OIL1',1);


select supp,com,SUM(cnt)
from inv_supplier
GROUP BY supp,com

A OIL1 1
A OIL2 1
A OIL3 1
A OIL4 1
B OIL1 1
B OIL3 1



select supp,SUM(cnt)
from inv_supplier
GROUP BY supp



Instead of 4 and 2 count of the respective supplier
is it possible to show 1 each for this supplier

A 1
B 1
  • 1. Re: QUERY
    jeneesh Guru
    Currently Being Moderated
    select supp,1 cnt
    from inv_supplier
    GROUP BY supp
  • 2. Re: QUERY
    908002 Expert
    Currently Being Moderated
    select distinct supplier , 1 from inv_supplier; -- ???? what is the logic behind it...
  • 3. Re: QUERY
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,
    This can be one way..
    SQL> select supp,com,SUM(cnt)
      2  from inv_supplier
      3  GROUP BY supp,com
      4
    SQL> /
    
    SUPP                           COM                              SUM(CNT)
    ------------------------------ ------------------------------ ----------
    B                              OIL1                                    1
    A                              OIL2                                    1
    A                              OIL3                                    1
    A                              OIL1                                    1
    B                              OIL3                                    1
    A                              OIL4                                    1
    
    6 rows selected.
    
    SQL>
    SQL> select distinct supp,SUM(cnt)
      2  from inv_supplier
      3  GROUP BY supp
      4
    SQL> /
    
    SUPP                             SUM(CNT)
    ------------------------------ ----------
    B                                       1
    A                                       1
    
    SQL>
  • 4. Re: QUERY
    852469 Newbie
    Currently Being Moderated
    in my case it is coming 4 and 2
  • 5. Re: QUERY
    852469 Newbie
    Currently Being Moderated
    it should be

    select supp,SUM(distinct cnt)
    from inv_supplier
    GROUP BY supp

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points