Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

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.

User generated image
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dsacker
dsacker
Flag of United States of America image
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

Avatar of Wilder1626
Wilder1626
Flag of Canada image

ASKER

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