1 Reply Latest reply: Apr 20, 2011 5:53 AM by dushyant.icon RSS

    Using group by clause

      Im new in QV and i need to know something.

      Lets say i have table like this:

      id, prodID, A, B, C, D, E
      01, 11A1, 100, 1, , 500, 2010
      01, 11A1, 200, , 5, 700, 2010
      01, 11A1, ,7, , 2010

      now i need to group them. So do i need to use max(), count(), sum() all fields except id, prodID?
      if i have many fields it's making trouble right?

        • Using group by clause

          Hi Mucho,

          I think this code may help you to use group by and order by in your scrips:

          Load 'IP Test Volume & RevenueWIP1' as Volumename,name as testname,billdate AS TESTDATE,price as Testamount, itemid as Testvol;
          select t.name,ipbid.itemid,ipbid.price,ipb.billdate
          from ipbill ipb inner join ipbillpackageitemdetail ipbid on ipb.billno = ipbid.billno
          inner join test t on ipbid.itemid = t.id
          where ipbid.serviceid = 8 AND Year(ipb.billdate) = Year(GETDATE())-1;

          //IP Test Volume & Revenue within Package
          Load 'IP Test Volume & RevenueWIP2' as Volumename,name as testname,billdate AS TESTDATE,(apolloprice - categoryprice) as Testamount, itemid as Testvol;
          select t.name,ipbid.itemid,ipbid.apolloprice, ipbid.categoryprice,ipb.billdate
          from ipbill ipb inner join ipbillpackageitemdetail ipbid on ipb.billno = ipbid.billno
          inner join test t on ipbid.itemid = t.id
          where ipbid.serviceid = 8 AND Year(ipb.billdate) = Year(GETDATE())-1;

          Volume:
          noconcatenate Load Volumename,testname,date(TESTDATE) as TESTDATE,Sum(Testamount) as Testamount, sum(Testvol) as Testvol resident Volume_TEMP group by Volumename,testname,TESTDATE;
          drop table Volume_TEMP;
          store Volume into [..\DataSources\QVDOLD\Volumeinfo.QVD](qvd);
          drop table Volume;

          Please let me know if that will serve your purpose.