There must be a better way

Question

There must be a better way

Don Bernstein

Old Hand

Points: 393
More actions
November 10, 2008 at 1:23 pm

#196176

I have a linkage table with 2 columns: StudentID, CustomID.
I want to use a Print statement to show the number of students (StudentID's) in the table.
I could not make a subquery work. Here's what works:
Declare @StudentCountAs Int
Select StudentIDInto tmpStudentCount
From LinkageTable
Group By StudentID
Select @StudentCount = Count(*) From tmpStudentCount
Print 'Student Count: ' + Replace(Cast(Convert(money, @StudentCount, 1) as Varchar(20)), '.00', '')
Questions:
1) Is there a way to do this without an extra table (tmpStudentCount)?
2) Is there a better way to format the count with commas?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Don Bernstein (11/10/2008)
I have a linkage table with 2 columns: StudentID, CustomID.
I want to use a Print statement to show the number of students (StudentID's) in the table.
I could not make a subquery work. Here's what works:
Declare @StudentCountAs Int
Select StudentIDInto tmpStudentCount
From LinkageTable
Group By StudentID
Select @StudentCount = Count(*) From tmpStudentCount
Print 'Student Count: ' + Replace(Cast(Convert(money, @StudentCount, 1) as Varchar(20)), '.00', '')
Questions:
1) Is there a way to do this without an extra table (tmpStudentCount)?
2) Is there a better way to format the count with commas?

1)

declare @StudentCount int;

select @StudentCount = count(distinct StudentID) from LinkageTable;

print 'Student Count: ' + cast(@StudentCount as varchar(8));

Don Bernstein Old Hand Points: 393 More actions · Answer 2

Thanks Lynn,

That answers the main part of my question nicely.

I'd still like to see an answer to part 2 - a simpler way to format an integer with commas than

Replace(Cast(Convert(money, @StudentCount, 1) as Varchar(14)), '.00', '').

Don Bernstein Old Hand Points: 393 More actions · Answer 3

Actually, the Convert(money, Value, 1) does not insert commas either. I was going by sql 2000 docs.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

I know it's not always possible - but putting the thousand separators into numbers is a UI function, so it's usually best ot leave that up to the UI to handle. Most UI's have built-in stuff to do that, whereas (like you just found out) it's not straightforward "from the backend".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Don Bernstein Old Hand Points: 393 More actions · Answer 5

My bad. It works right when you run it right:

Print Convert(Varchar(14), Cast(@StudentCount as money), 1)

or

Print Replace(Convert(Varchar(14), Cast(@StudentCount as money), 1), '.00', '')

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Looks good to me. 🙂

I'm deleting my earlier entry to plug in the commas.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills