SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Return column name and count
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Alain_TV
Starting Member

3 Posts

Posted - 11/03/2013 :  02:47:22  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/03/2013 :  03:03:28  Show Profile  Reply with Quote

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
Go to Top of Page

Alain_TV
Starting Member

3 Posts

Posted - 11/03/2013 :  03:11:05  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/03/2013 :  03:16:51  Show Profile  Reply with Quote
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
Go to Top of Page

Alain_TV
Starting Member

3 Posts

Posted - 11/03/2013 :  03:33:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/03/2013 :  04:13:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000