Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

select text field in query with aggregate function

Posted on 2004-08-02
3
Medium Priority
?
1,850 Views
Last Modified: 2012-08-14
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
Comment
Question by:cdhbs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 1000 total points
ID: 11700769
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
 

Author Comment

by:cdhbs
ID: 11700779
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
 
LVL 34

Accepted Solution

by:
arbert earned 1000 total points
ID: 11701324
Yes, you could count in a subquery--those are about the only options...
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question