Using group by clause

Wed, 20 Apr 2011 09:35:26 GMT

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

Wed, 20 Apr 2011 09:53:40 GMT

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.

topic Using group by clause in QlikView

Using group by clause

Using group by clause