group by

select to_date('2004/02/11 07:32:00', 'YYYY/MM/DD HH24:MI:SS'),count (*) from XXX group by XXX;

How can i  reference the group by on a date column? Please help!
crishna1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

reginabCommented:
Use Functions like YEAR() or DatePart() and group by this.
An examle:
SELECT YEAR(Date_Column),SUM(Sum_Column) FROM tbl GROUP BY YEAR(Date_Column)
0
TimYatesCommented:
Does:

 select to_date('2004/02/11 07:32:00', 'YYYY/MM/DD HH24:MI:SS') AS DT,count (*) from yourtable group by DT;

work?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomgalloCommented:
Try this:

SELECT
   to_date('2004/02/11 07:32:00', 'YYYY/MM/DD HH24:MI:SS'),
   count (*)
FROM
   XXX
GROUP BY to_date('2004/02/11 07:32:00', 'YYYY/MM/DD HH24:MI:SS')

what DB engine are you using?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

TimYatesCommented:
> what DB engine are you using?

This is a good question, as different databases use different flavours of SQL...
0
tomgalloCommented:
Is a good question, because of internal functions or anything. If you are using PL/SQL or T-SQL the functions changes. For example in MS T-SQL there are several options that in Sybase T-SQL doesn't work.
Besides the question doesn't have to be a "good" one... is only a question. In generarlly speaking the questions are only for answer them, not judging them.
0
tomgalloCommented:
Venabili,

I object... because I tried the TimYates solution in Sybase and doesn't seem to work.
The mine works in Sybase(T-SQL).

-tom
0
TimYatesCommented:
Mine will work in Oracle and Postgres (I think)
0
tomgalloCommented:
It's  a split then...
Tim in PL/SQL, mine T-SQL

-tom
0
TimYatesCommented:
Doesn't sybase have the "AS" keyword then?
0
tomgalloCommented:
Yes it has...

But you can not referer to an AS field in a group by clause. You have to be explicit.

-tom
0
TimYatesCommented:
Fair enough :-)

A split between me and you seems more than fair then :-)

Tim
0
fmarkovicCommented:
by the way, this question does not make sense.
What is the author trying to do anyway ?? - group-by column has to be referenced in the 'select' part!

According to the ANSI, there are 2 syntaxes for group-by clause:
  1) use full column name : group by col_name
  2) use relative number of the column in select clause: group by 1


0
tomgalloCommented:
The question is about how to do a Group By clause, and the answers are right for T-SQL and PL/SQL

-tom
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.