Solved

SQL select

Posted on 2007-03-29
8
191 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 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 143

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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 143

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 143

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
scheduler notification 9 79
When are cursors useful? 8 61
DMV Script to find how many times statistics are utilized 2 30
DB Shutdown Automatically 11 34
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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…

734 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