Solved

SQL select

Posted on 2007-03-29
8
194 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
Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

 
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

Linux Academy Android App Now Supports Chromecast

We have some fantastic news for our Android fans. We’re so excited to announce that the Linux Academy Android app is now available with Chromecast support. That’s right – simply download the latest update of the Linux Academy App and start casting your favorite course videos!

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

622 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