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.
| Author |
Topic |
|
label
Posting Yak Master
197 Posts |
 Posted - 2003-02-18 : 14:46:06
|
| The query below returns an error stating that "Total" and "FullName" are not valid columns. "select username, count(username) as Total, (us.firstname + ' ' + us.lastname) as FullName Fromsmc_new_products.dbo.etechmodelrequests etjoin smc_new_products.dbo.usr_smc us on et.[username]=convert(varchar(50), us.[user_id])group by username, total, FullNameorder by total desc"I want to return the username, the total occurences and the user's fullname as a single line to populate a Dataset. What I wrote above looks like it should work but obviously there's something about Group By's that I'm not quite understanding. Any one have any ideas on how to get the above query to work? Thanks. |
|
|
label
Posting Yak Master
197 Posts |
Posted - 2003-02-18 : 14:57:19
|
| Nevermind, the below worked. "select username, count(username) as Total, (firstname + ' ' + lastname) as FullnameFromsmc_new_products.dbo.etechmodelrequests etjoin smc_new_products.dbo.usr_smc us on et.[username]=convert(varchar(50), us.[user_id])group by username, firstname, lastname"Not sure why I can't refer to declared field names like that but at least it works. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-18 : 15:05:43
|
| Label:Take a glance at the GROUP BY Sectiopn in the BOL.You can't group by the total....That's why you are doing the grouping in the first place. You are counting the number of things for that group. And why not Count(*)? I'm not sure if there's additional overhead, but using Count(column_name) is usefull when doing other functions like distinct.BOL Says:*Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.Hope this helps, and good luckBrett8-) |
|
|
|
|
|
|
|
|
Group By problem...