TSQL: Select::

Question

Post reply

TSQL: Select::

John Esraelo-498130

SSCertifiable

Points: 5914
More actions
November 20, 2009 at 10:47 am

#228046

I have an interesting one..
is there a way to suppress the expressions from displaying in a select statement that at the end calculates the total sum...
Just want to see the sum / total nothing else..
here it is..
I have
select ..., foobar
from foobarVilleTable
compute sum(foobar)
You may be asking why? I am glad that you asked.. the list is in millions and kind of trying to avoid the verbowse part of that..
😀
Cheers,
John Esraelo

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you are looking for?

SELECT entry AS ' ', SUM(Entryid) AS ' ' FROM foobarVille GROUP BY entry

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 2

I have

select

200-len(tmessage) wastedspace

from mytable

compute sum(200-len(tmessage))

I hope this is clear..

thx

Cheers,
John Esraelo

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Is this what you mean?

CREATE TABLE #T(tmessage VARCHAR(200))

INSERT INTO #T

SELECT 'Short' UNION ALL

SELECT 'Longer' UNION ALL

SELECT 'Even a longer message' UNION ALL

SELECT 'A realy realy realy extra long message'

select SUM(200 -Len(tmessage)) AS WASTED,AVG(200 -Len(tmessage)) AS 'Average Wasted'

FROM #T

DROP TABLE #T

The results:

WASTEDAverage Wasted

730 . . . . . 182

If not then please refer to the link in my signature block and post the table definition, some sample data, so that those who want to help you can help you .

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 4

thank you for the reply.

yes, this absolutely works great.

I was just wondering about the COMPUTE method and if there was a way not to show the PART ONE of the TWO of which the first section shows the detail of the records and the section two as you know then will show the total.

but, I believe I will use this method of course.

thx a lot

ciao

Cheers,
John Esraelo

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Now I do not really know why you are doing these calculations, but you could add one more item to the code to give you an overall view of the length of the contents of that table column:

CREATE TABLE #T(tmessage VARCHAR(200))

INSERT INTO #T

SELECT 'Short' UNION ALL

SELECT 'Longer' UNION ALL

SELECT 'Even a longer message' UNION ALL

SELECT 'A realy realy realy extra long message truly really long'

select SUM(200 -Len(tmessage)) AS WASTED,AVG(200 -Len(tmessage)) AS 'Average Wasted',

MIN(200 -Len(tmessage)) AS 'Minimum Wasted'

FROM #T

DROP TABLE #T

WASTEDAverage WastedMinimum Wasted

. . 712 . . . . . 178 . . . . . . .144

But if you are considering altering the size of columns in a existing table, the above could be more useful.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

As a bit of a side bar... take a look at the WITH ROLLUP and WITH CUBE options on the GROUP BY and the GROUPING function that goes along with it. There's a world of summarized information in those options that are datamart quality without most of the hassle and they blow the COMPUTE clause away. You can use WITH ROLLUP or WITH CUBE in a view and then just select the totals you want, etc, etc, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 7

wow.. it looks like I have lots of work to do.. if you will excuse me..

:))

excellent tip and I am on my way back to the classroom..

thx

Cheers,
John Esraelo