Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL: Select:: Expand / Collapse
Author
Message
Posted Friday, November 20, 2009 10:47 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #822563
Posted Friday, November 20, 2009 11:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
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
Post #822602
Posted Friday, November 20, 2009 12:10 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
I have

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

I hope this is clear..
thx


Cheers,
John Esraelo
Post #822615
Posted Friday, November 20, 2009 3:32 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
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
Post #822758
Posted Friday, November 20, 2009 3:49 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #822762
Posted Friday, November 20, 2009 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:50 PM
Points: 5,322, Visits: 25,235
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
Post #822767
Posted Friday, November 20, 2009 6:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 35,216, Visits: 31,673
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #822805
Posted Friday, November 20, 2009 7:20 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 2:58 PM
Points: 531, Visits: 965
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
Post #822812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse