Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL: Select::


TSQL: Select::

Author
Message
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 1029
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..
:-D

Cheers,
John Esraelo
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 25280
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

Before posting a performance problem please read
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 1029
I have

select
200-len(tmessage) wastedspace
from mytable
compute sum(200-len(tmessage))

I hope this is clear..
thx

Cheers,
John Esraelo
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 25280
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:
WASTED Average 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

Before posting a performance problem please read
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 1029
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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 25280
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



WASTED Average Wasted Minimum 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

Before posting a performance problem please read
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45456 Visits: 39946
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
John Esraelo-498130
John Esraelo-498130
Mr or Mrs. 500
Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)Mr or Mrs. 500 (549 reputation)

Group: General Forum Members
Points: 549 Visits: 1029
wow.. it looks like I have lots of work to do.. if you will excuse me..

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

Cheers,
John Esraelo
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search