Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL select

Posted on 2007-03-29
8
Medium Priority
?
196 Views
Last Modified: 2010-03-20
Hi,

I have a column
TS_ACCESS_TIME (type = DATE)
DS_TITLE

I need to group the requests by DS_TITLE and by month
and another select by DS_TITLE and by day

Example :

01/07/2006 18:54:10       TITLE_1
01/07/2006 13:52:10       TITLE_1
03/07/2006 11:42:54       TITLE_1
23/05/2006 10:32:24       TITLE_1
15/03/2006 09:32:11       TITLE_2

I want this :

3    TITLE_1   07/2006 (<-month)
1    TITLE_1   05/2006
1    TITLE_2   03/2006


And for the other select :

2    TITLE_1  01/07/2006 (<- day 1 of month 07)
1    TITLE_1  03/07/2006
1    TITLE_1  23/05/2006
1    TITLE_2  15/03/2006


Thank u for any help
0
Comment
Question by:matthew016
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18815201
please try the following 2 queries:

select count(*), DS_TITLE, FORMAT(TS_ACCESS_TIME, "YYYY/MM")
FROM yourtable
group by DS_TITLE, FORMAT(TS_ACCESS_TIME, "YYYY/MM")


select count(*), DS_TITLE, FORMAT(TS_ACCESS_TIME, "YYYY/MM/DD")
FROM yourtable
group by DS_TITLE, FORMAT(TS_ACCESS_TIME, "YYYY/MM/DD")
0
 
LVL 9

Author Comment

by:matthew016
ID: 18815228
ora-00904

"FORMAT" : invalid identifier
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18815241
as you did not specify the database.... :-(

here comes the oracle version:



select count(*), DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'MM/YYYY')
FROM yourtable
group by DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'MM/YYYY')


select count(*), DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'DD/MM/YYYY')
FROM yourtable
group by DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'DD/MM/YYYY')
0
RHCE - Red Hat OpenStack Prep Course

This course will provide in-depth training so that students who currently hold the EX200 & EX210 certifications can sit for the EX310 exam. Students will learn how to deploy & manage a full Red Hat environment with Ceph block storage, & integrate Ceph into other OpenStack service

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18815248
I see that you crossposted in oracle, however that was not visible to me...
I hope EE will make that change soon, brings too much confusion !
0
 
LVL 9

Author Comment

by:matthew016
ID: 18815276
ora-00904

"TS_ACCESS_TIME"  :  invalid identifier
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18815335
if you get that error, you posted a wrong column name.
0
 
LVL 9

Author Comment

by:matthew016
ID: 18815361
My columns are correct,
I did other queries that worked fine with DS_TITLE


SELECT COUNT(*), DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'MM/YYYY')
FROM TB_WEB_STAT
WHERE DS_TITLE IS NOT NULL
GROUP BY DS_TITLE, TO_CHAR(TS_ACCESS_TIME, 'MM/YYYY')

(
for example this query works fine :
SELECT COUNT(*) AS COUNT, DS_TITLE AS NAME,'title' AS TYPE
FROM TB_WEB_STAT
WHERE DS_TITLE IS NOT NULL
GROUP BY  DS_TITLE
)
0
 
LVL 9

Author Comment

by:matthew016
ID: 18815367
Oh sorry i just understand what u meant ...
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

670 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