5 Replies Latest reply: Jan 4, 2013 3:23 AM by 852469 RSS

    QUERY

    852469
      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
          select supp,1 cnt
          from inv_supplier
          GROUP BY supp
          • 2. Re: QUERY
            908002
            select distinct supplier , 1 from inv_supplier; -- ???? what is the logic behind it...
            • 3. Re: QUERY
              Chanchal Wankhade
              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
                in my case it is coming 4 and 2
                • 5. Re: QUERY
                  852469
                  it should be

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