What am I doing wrong now.....

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

What am I doing wrong now.....

Author

Topic

label
Posting Yak Master

197 Posts

Posted - 2003-03-31 : 07:55:48

I have the following query:

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) as [Total]
from
usr
group by uct
order by user_create_time desc
compute avg([Total])

I simply want to create a report that gives the each date (just the mm/dd/yy date....not time) and how many registered on each date and then print the average.

Several things don't seem to be working. I can't group by the column 'uct' because for some reason it doesn't seem to recognize that as a column despite the fact I just declared it earlier. Also, the compute avg doesn't work either for the same type of reasons.

What am I missing?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 08:11:01

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) as [Total]
from
usr
group by convert(varchar(10), user_create_time, 101)
order by user_create_time desc
compute avg([Total])

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-03-31 : 08:18:16

quote:

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) as [Total]
from
usr
group by convert(varchar(10), user_create_time, 101)
order by user_create_time desc
compute avg([Total])

I get the following two errors when I run that:

1. Column name 'usr.user_create_time' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

2. Invalid column name 'Total'.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-03-31 : 08:22:23

Try group by 1 - that should tell it to group by 1st column

Try taking out the "as" and just naming it [total].

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) [Total]
from
usr
group by 1
order by user_create_time desc
compute avg([Total])

I haven't had a chance to test - try it and see.

Ciao

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 08:24:32

ooops. sorry i just focused on the GROUP BY problem, not the rest:

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) as [Total]
from
usr
group by convert(varchar(10), user_create_time, 101)
order by count(convert(varchar(10),user_create_time,101)) DESC
compute avg([Total])

Note sure how to handle the compute ... I never use compute because I like only returning 1 recordset at a time ... but chances are it is the same problem as the group by -- you need to spell out what the definition is for the [Total] field, not just use the alias.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 08:26:35

Wanderer --

I don't think you can GROUP BY 1 with 1 being the first column .... that is for order by's only.

Also, note the order by needs the COUNT(*) in there, since we need to ordering by the result of an aggregate function... however, in this case, you COULD say ORDER BY 2.

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-03-31 : 08:27:46

quote:

select convert(varchar(10), user_create_time, 101) as uct, count(convert(varchar(10), user_create_time, 101)) [Total]
from
usr
group by 1
order by user_create_time desc
compute avg([Total])

When I run that, I get the following error:

1. Server: Msg 164, Level 15, State 1, Line 5
GROUP BY expressions must refer to column names that appear in the select list.

2. Invalid column name 'Total'.

label
Posting Yak Master

197 Posts

Posted - 2003-03-31 : 08:29:52

quote:
Note sure how to handle the compute ... I never use compute because I like only returning 1 recordset at a time ... but chances are it is the same problem as the group by -- you need to spell out what the definition is for the [Total] field, not just use the alias.

I'm unclear what you mean by "spell out the definition, not just the alias".

How would I go about being more specific beyond using the alias I've given the results?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-31 : 10:04:47

Repeat the expression in your compute() clause, don't just reference the alias. Exactly like you had to do with the ORDER BY and the GROUP BY clauses.

Don't do the GROUP BY 1 -- that will NOT work, as you have found out. Look at my previous post (the one just before where I mentioned that GROUP BY 1 will not work) for most of the correct statement; as i mentioned, play around with the compute() or hopefully someone else can help you.

- Jeff

label
Posting Yak Master

197 Posts

Posted - 2003-03-31 : 12:57:37

quote:

Repeat the expression in your compute() clause, don't just reference the alias. Exactly like you had to do with the ORDER BY and the GROUP BY clauses.

Alrighty. Got them both working now.

Thanks very much for your help!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2003-04-01 : 02:47:45

Hmmm - sorry the "group by 1" didn't work - I did say I hadn't tested it - I was hoping it might be consostent with the " order by " function.

Jeff - Regards the order by - I just copied your SQL, unfortunately before you changed it ...

Sorry my SITD (Shot-In-The-Dark) didn't work out, but I'm glad you resolved your problem. What resolved the " as [total] " problem ? Was it removing the AS, or did it work after fixing the group by issue ?

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources