Return column name and count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return column name and count

Author

Topic

Alain_TV
Starting Member

12 Posts

Posted - 2013-11-03 : 02:47:22

Hi All,

I'm starting to use SQL 2008 recently, and I'm just having trouble with the following problem:

The following query:

SELECT t_Category.Name as [Category]
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID

Returns this result:

Category

BMS
BMS
Water
BMS
BMS
Air

And the following query:

SELECT COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Category_ID

Returns this result:

AssetQty

4
1
1

I need to have both of those results returned, as a single result. Such as:

Category AssetQty

BMS 4
WATER 1
AIR 1

However, I'm not able to, due to the fact, that if I add the "t_Category.Category.Name" in the SELECT clause, it gives me the following error:

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

And if I try to use the "Name" as part of the count clause, it won't work, as text are not acceptable data types for aggregations.

I'm sure, it is something simple, but is driving me crazy.

Any help is appreciated.

Regards,
Alain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 03:03:28

[code]
SELECT t_Category.Name,COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Alain_TV
Starting Member

12 Posts

Posted - 2013-11-03 : 03:11:05

quote:
Originally posted by visakh16

SELECT t_Category.Name,COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY t_Category.Name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Hi visakh16,

Thanks for the reply. Unfortunately, this option does not work, as per my comments above.
I get the following error, from your code:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

Regards,
Alain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 03:16:51

ah missed that last sentence. Still if its sql 2008 you can use this


SELECT CAST(t_Category.Name AS varchar(max)) AS CategoryName,COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY CAST(t_Category.Name AS varchar(max))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Alain_TV
Starting Member

12 Posts

Posted - 2013-11-03 : 03:33:55

quote:
Originally posted by visakh16

ah missed that last sentence. Still if its sql 2008 you can use this

SELECT CAST(t_Category.Name AS varchar(max)) AS CategoryName,COUNT(t_Category.Category_ID) AS AssetQty
FROM t_Assets, t_Category, t_Priority, t_Location, t_User_Assets
WHERE t_Assets.Asset_ID = t_User_Assets.Asset_ID
AND t_Category.Category_ID = t_User_Assets.Category_ID
AND t_Priority.Priority_ID = t_User_Assets.Priority_ID
AND t_Location.Location_ID = t_User_Assets.Location_ID
GROUP BY CAST(t_Category.Name AS varchar(max))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Thanks a lot, that actually worked like a treat!!

Do you mind explaining something...?

I get that, you are casting the name field, to get around the issue with SQL not accepting text fields as part of an aggregate function. Should I be changing those fields to VarChar instead of Text fields? I was reading that should not be used anymore. I guess, there would be not need for casting.

Thanks again for your help.

Regards,
Alain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-03 : 04:13:09

yes.. Indeed
text,ntext etc are deprecated so you should be using varchar(max),nvarchar(max) etc instead of them in newer versions above 2005

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources