• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1852
  • Last Modified:

select text field in query with aggregate function

How do I include a text (or ntext) field in a query that uses an aggregate function

eg

SELECT textField, someOtherField, COUNT(anotherFieldAgain) AS Total
FROM tblTable
GROUP BY textField, someOtherField

In MS access I could use the FIRST or LAST function to do this (on a SQL Server back-end) - and not include it in the group by clause.  How do I do this in SQL Server?
0
cdhbs
Asked:
cdhbs
  • 2
2 Solutions
 
arbertCommented:
The best you can really do is to do a cast/convert on it:


SELECT convert(varchar(8000),textField), someOtherField, COUNT(anotherFieldAgain) AS Total
FROM tblTable
GROUP BY convert(varchar(8000),textField), someOtherField


Of course, you loose anything over 8000 bytes.....Why do you need to group on a text field???
0
 
cdhbsAuthor Commented:
It's not so much that I want to group on the field.  What I'm doing is counting how many records there are in related tables and outputting all the data in this table.  Maybe I could count the related records in a subquery?
0
 
arbertCommented:
Yes, you could count in a subquery--those are about the only options...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now