Multiple Grand Totals

Database

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

4 REPLIES
Enthusiast

Re: Multiple Grand Totals

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

Senior Apprentice

Re: Multiple Grand Totals

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 :-)

Re: Multiple Grand Totals

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

Senior Apprentice

Re: Multiple Grand Totals

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  ;-)