Oracle 11g - Count order_id per dates issue

Hi

I have this below SQL were i want to pull the number of ORDER_ID created per REC_CREATE_TMS.

SELECT DISTINCT (TO_CHAR(REC_CREATE_TMS, 'yy-mm-dd')) AS date_created,
                Count(ORDER_ID)                       AS count
FROM   EXOMGR.EXO_CONF_ORDERS
WHERE  REC_CREATE_TMS >= to_date('14-04-29', 'yy-mm-dd')
GROUP  BY REC_CREATE_TMS
ORDER  BY (TO_CHAR(REC_CREATE_TMS, 'yy-mm-dd'));

Open in new window

The problem i have based on below picture is that i still get multiple counts for the same date.

How can i fix this?

Thanks for your help.

Oracle DatabaseSQL

Last Comment

Wilder1626

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

4/14/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

dsacker

4/14/2015

You won't need DISTINCT. This will fix it:

SELECT TO_CHAR(REC_CREATE_TMS, 'yy-mm-dd') AS date_created,
                Count(ORDER_ID)                       AS count
FROM   EXOMGR.EXO_CONF_ORDERS
WHERE  REC_CREATE_TMS >= to_date('14-04-29', 'yy-mm-dd')
GROUP  BY TO_CHAR(REC_CREATE_TMS, 'yy-mm-dd')
ORDER  BY TO_CHAR(REC_CREATE_TMS, 'yy-mm-dd');

Open in new window

Wilder1626

4/14/2015

ASKER

wow, just that!!!!!! Thanks a lot for your help.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck