[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

group by

Posted on 2004-11-15
14
Medium Priority
?
565 Views
Last Modified: 2006-11-17
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!
0
Comment
Question by:crishna1
13 Comments
 
LVL 4

Expert Comment

by:reginab
ID: 12589241
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
 
LVL 35

Accepted Solution

by:
TimYates earned 750 total points
ID: 12604041
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
 
LVL 2

Assisted Solution

by:tomgallo
tomgallo earned 750 total points
ID: 12618508
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Expert Comment

by:TimYates
ID: 12623017
> what DB engine are you using?

This is a good question, as different databases use different flavours of SQL...
0
 
LVL 2

Expert Comment

by:tomgallo
ID: 12627475
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
 
LVL 2

Expert Comment

by:tomgallo
ID: 12820446
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
 
LVL 35

Expert Comment

by:TimYates
ID: 12820525
Mine will work in Oracle and Postgres (I think)
0
 
LVL 2

Expert Comment

by:tomgallo
ID: 12820580
It's  a split then...
Tim in PL/SQL, mine T-SQL

-tom
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12820612
Doesn't sybase have the "AS" keyword then?
0
 
LVL 2

Expert Comment

by:tomgallo
ID: 12820676
Yes it has...

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

-tom
0
 
LVL 35

Expert Comment

by:TimYates
ID: 12820711
Fair enough :-)

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

Tim
0
 
LVL 2

Expert Comment

by:fmarkovic
ID: 12822253
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
 
LVL 2

Expert Comment

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

-tom
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

Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month17 days, 21 hours left to enroll

829 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