use column in Group By, but Hide column from the results set

Question

use column in Group By, but Hide column from the results set

preetid2

Ten Centuries

Points: 1088
More actions
September 19, 2012 at 10:13 am

#267888

Is this possible in sql server 2008?
I want to use the column in the select, and group by. But want to hide the column from the results set?

Viewing 13 posts - 1 through 12 (of 12 total)

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

preetid2 (9/19/2012)
Is this possible in sql server 2008?
I want to use the column in the select, and group by. But want to hide the column from the results set?

No. If you select a column, it will appear.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Yes it is possible, but what it will give you?

SELECT Col1, Count_Rec

FROM (SELECT Col1, Col2, Count(*) AS Count_Rec

FROM MyTable

GROUP BY Col1, Col2) aq

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

laurie-789651

SSCertifiable

Points: 7680

September 19, 2012 at 10:24 am

#1539681

Can you give an example of what you're thinking of?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Eugene Elutin (9/19/2012)
Yes it is possible, but what it will give you?
SELECT Col1, Count_Rec
FROM (SELECT Col1, Col2, Count(*) AS Count_Rec
FROM MyTable
GROUP BY Col1, Col2) aq

Haha, what a fantastically useful query this would be 😀

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Phil Parkin (9/19/2012)
Eugene Elutin (9/19/2012)
Yes it is possible, but what it will give you?
SELECT Col1, Count_Rec
FROM (SELECT Col1, Col2, Count(*) AS Count_Rec
FROM MyTable
GROUP BY Col1, Col2) aq
Haha, what a fantastically useful query this would be 😀

Who knows, who knows... :hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

You get too complicated. This should work.

This will also give incomplete information on how the groups are made.

SELECT Col1, Count(*) AS Count_Rec

FROM MyTable

GROUP BY Col1, Col2

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

preetid2 Ten Centuries Points: 1088 More actions · Answer 7

preetid2

Ten Centuries

Points: 1088

September 19, 2012 at 1:39 pm

#1539775

Thanks Phil! this worked perfect!

Phil Parkin SSC Guru Points: 246985 More actions · Answer 8

preetid2 (9/19/2012)
Thanks Phil! this worked perfect!

I feel like I have entered a parallel universe.:alien:

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

Phil Parkin (9/19/2012)
preetid2 (9/19/2012)
Thanks Phil! this worked perfect!
I feel like I have entered a parallel universe.:alien:

I knew that! Your avatar makes everything right! What a solution!

:hehe::hehe::hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

siggemannen SSCrazy Points: 2436 More actions · Answer 10

These kind of queries have real usage, for example if you want to preserve "duplicate rows" of Col1, Col2, which cannot be preserved if you only Group By Col1

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

siggemannen (9/20/2012)
These kind of queries have real usage, for example if you want to preserve "duplicate rows" of Col1, Col2, which cannot be preserved if you only Group By Col1

Why would you want that?

You would be missing information.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Phil Parkin SSC Guru Points: 246985 More actions · Answer 12

siggemannen (9/20/2012)
These kind of queries have real usage, for example if you want to preserve "duplicate rows" of Col1, Col2, which cannot be preserved if you only Group By Col1

Grouping By something which is not in the select is fine, I'm sure there are plenty of uses for that.

But the original requirement - to include a column in select and in group by but then not to display it - is not so fine. I cannot think of a single use for that, other than as an example for a 'how to write superfluous SQL' book.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel