SQL select

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
LVL 9
matthew016Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthew016Author Commented:
ora-00904

"FORMAT" : invalid identifier
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
matthew016Author Commented:
ora-00904

"TS_ACCESS_TIME"  :  invalid identifier
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if you get that error, you posted a wrong column name.
0
matthew016Author Commented:
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
matthew016Author Commented:
Oh sorry i just understand what u meant ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.