How to combine aggregate function with * in SELECT?

Question

Post reply

How to combine aggregate function with * in SELECT?

sg2000-429020

Ten Centuries

Points: 1106
More actions
January 18, 2008 at 3:18 pm

#202489

I tried to execute the following SQL statement but failed:
SELECT COUNT(*), * FROM TableA WHERE Type = X
I can do the following and it is OK
SELECT COUNT(*), Col1, Col2, Col3 ... Col20 FROM TableA WHERE Type = X GROUP BY Col1, Col2, Col3 ... Col20
There are 20 columns in TableA, and it would very messy to use the GROUP BY clause for each column.
Would appreciate suggestion from you. Thanks in advance.
sg2000

Viewing 6 posts - 1 through 5 (of 5 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

I would be helpful if you explained what you are trying to do.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

If you have 20 columns in table A, and you try to list all 20 columns in the presence of a COUNT, then you must list all 20 columns in the SELECT and the GROUP BY. If there happens to be either a Primary Key or a unique index anywhere in the table, the count will always be "1" because you're listing/grouping by all 20 columns.

Let me rephrase what Michael said in his post (above) just a wee bit... what is it that you really want to do?

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

sg2000-429020 Ten Centuries Points: 1106 More actions · Answer 3

Thanks for the quick response. This is what I was trying to do:

I have a table (TableA) that contains 20 columns (Col1, Col2 through Col20). I want to retrieve 5 of (Col1, Col5, Col7, Col10 and Col 20) of the 20 columns and also want to know how many of these 5 columns contain Null. Since I know the primary key of the row, I will retrieve only one row. I use the following SQL statement:

SELECT NullCount= COUNT(Col1) + COUNT(Col5) + COUNT(Col7) + COUNT(Col10) + COUNT(20), Col1, Col5, Col7, Col10, Col20 FROM TableA WHERE ID = x GROUP BY Col1, Col5, Col7, Col10, Col20

This works OK. However, if I want to count and retrieve 18 columns, the SQL statement would become very long and messy.

My question is: is there any way I can achieve this goal without the GROUP By phrase?

Thanks in advance for any suggestions.

sg2000

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

My question is: is there any way I can achieve this goal without the GROUP By phrase?

I could be wrong, but I don't think there is...

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Actually - I'm not sure what you plan on getting with that. The numbers are going to be all over the place aren't they?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?