How to get certain columns in combination with a commonly used SQL snippet statement

Question

1.00/5 (1 vote)

See more:

So, at this particular time, I cannot find a way to include certain column names in a statement that I have been using.

Here is the code:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows), 
	[Columnname] = c.name
FROM 
    sysobjects so, 
    sysindexes si, 
    syscolumns c
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

this is the original code without the syscloumns:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows)
FROM 
    sysobjects so, 
    sysindexes si	
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
  2 desc

I figure that I could use something like C.column, but it keeps giving me this error:

Column 'sysobjects.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

All I am trying to do is get row count per table and have certain columns like date of employment and products sold. but I just can't seem to use what I already have to include those columns.

What I have tried:

tried to add a syscolumns into the list but as stated above I keep getting an error.

Posted 4-Jun-17 1:33am

Member 11856456

Updated 4-Jun-17 2:48am

Add a Solution

Comments

[no name] 4-Jun-17 7:56am

[Columnname] = MIN(c.name)

Member 11856456 4-Jun-17 8:14am

Just tried that now, it's not giving me any specific column information and just repeats "acceptable_cursor_options" in each row.

[no name] 4-Jun-17 8:20am

Yep, so you Need to think about to include c.name in GROUP BY, and if you do so then use again your original [Columnname] = c.name

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2017-06-04T02:48:00

Solution 1

In SQL, the moment you are using an AGGREGATE function all columns in the SELECT list must be a product of such function or be included in the GROUP BY part...
c.name does neither...
That's for beginning... The other problem is your old syntax of JOIN (which will create a very expensive CROSS JOIN)... As there is no condition whatsoever on syscolumns it will multiple the result sets and grow it to an extreme size...
You also wasting resource by converting object name to object id - OBJECT_ID(...) - while you already have the id at hand from sysobjects...
And finally, it is unclear what is your goal with the second query?

Posted 4-Jun-17 2:48am

Kornfeld Eliyahu Peter

Comments

[no name] 4-Jun-17 9:08am

Good Explanation/points, a 5

Kornfeld Eliyahu Peter 4-Jun-17 9:09am

Thank you...

Member 11856456 4-Jun-17 14:55pm

so I will try to dray a diagram using brackets as column displays.

|Table Name|Row count|date of employment|Products sold|

using the sql reader function in vb.net to display the information in listview. Just an easy way to view data once I click on a database. I have everything set up except for an appropriate SQL statement. Is there a better way to get the data?

Kornfeld Eliyahu Peter 5-Jun-17 2:26am