Solved

SQL select

Posted on 2007-03-29
8
185 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
  • 4
  • 4
8 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

770 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