All Forums Database
spinage 2 posts Joined 11/12
16 Dec 2013
Multiple Grand Totals

Hello,
I have searched for days trying to find answer to this without any sucess.  
My Query:

select

        HOSTNAME

       ,LOCATION

       ,RACK_NUMBER

       ,RACK_POSITION

       ,RACK_UNITS

       ,POWER_WATTS

FROM TABLE

with sum(RACK_UNITS)(title 'Total Rack Units:')by location

with sum(VENDOR_MAX_WATT_POWER)(title 'Total WATT POWER:')by location

with sum(RACK_UNITS)(title 'Grand Total RUs:')

with sum(VENDOR_MAX_WATT_POWER)(title 'Grand Total WATT POWER:');

 

This will fail to run, but if I take out the last line it will run fine.  How in the heck do I get mutliple grand totals?

 

I would greatly appreciate any help with this.  I have spent hours and hours looking for a solution with no avail.

 

Thank very much,

Bill

 

 

Raja_KT 1246 posts Joined 07/09
16 Dec 2013

Hi,

It seems you miss out the BY + fieldname. I am not sure if it allows for multiple with sum too.

I test for onewith sum(xxx) by field1.... it works fine.

However, I test this way using. You can make change accordingly:

SUM(rack_units) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

 

select HOSTNAME,LOCATION,RACK_NUMBER,RACK_POSITION,RACK_UNITS,POWER_WATTS,

SUM(rack_units) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as rkunit,

SUM(VENDOR_MAX_WATT_POWER) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as tot1,

SUM(rack_units) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as rk1,

SUM(rack_units) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),

SUM(VENDOR_MAX_WATT_POWER) OVER (PARTITION BY location ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as tot2 from TABLE

 

 

Cheers,

 

Raja

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
16 Dec 2013

Hi Bill,
there's only one grand total.
But when you need multiple aggregates on the same level you can simply delimit them by comma:

with sum(RACK_UNITS)(title 'Total Rack Units:'),
     sum(VENDOR_MAX_WATT_POWER)(title 'Total WATT POWER:')by location
with sum(RACK_UNITS)(title 'Grand Total RUs:'),
     sum(VENDOR_MAX_WATT_POWER)(title 'Grand Total WATT POWER:');

Btw, are you actually still running BTEQ reports like this? I almost forgot about that syntax :-)

Dieter

spinage 2 posts Joined 11/12
17 Dec 2013

Thank you Raja and Dieter.  Dieter's response gave me the output I was looking for.
 
And yes, I am still using BTEQ for reports.  I know I could use Crystal Reports, but I've never been a fan of that software.  Is there some other reporting tool I should be looking at that gives me the ability to schedule and e-mail reports out?  I am just a shell nut, but willing to change if I find something that can do what I'm looking for.
 
Thanks again.

Bill
 

dnoeth 4628 posts Joined 11/04
17 Dec 2013

Hi Bill,
almost any reporting tool should be able to do what you want, but why changing it when it suits you?
I just wondered because such BTEQ reports used to be printed directly on that nice green-white endless-paper and now everybody wants some more fancy-looking, fully-colored, graphically appealing report  ;-)

Dieter

You must sign in to leave a comment.