sql oracle group by subquery

I get the same ecommerce number for each date. I am trying to get ecommerce value count depending on the date, which is different for each date as the total number is only 105 for all October, not 391958. Any idea how to group by the output of a subquery? Thank you!

SELECT   to_char(wcs1.start_tms,'DD/MM/YYYY') as dates,
(
    SELECT count(*) 
    FROM   ft_t_wcs1 wcs1,ft_t_stup stup 
    WHERE  stup.modl_id='ECOMMERC'
    AND    stup.CROSS_REF_ID=wcs1.acct_id
    AND    stup.end_tms IS NULL
) AS     ecommerce
FROM     ft_t_wcs1 wcs1, ft_t_stup stup
WHERE    wcs1.scenario='CREATE' 
AND      wcs1.acct_id IS NOT NULL 
AND      wcs1.start_tms BETWEEN add_months(TRUNC(SYSDATE,'mm'),-1) AND LAST_DAY(add_months(TRUNC(SYSDATE,'mm'),-1))
GROUP BY to_char(wcs1.start_tms,'DD/MM/YYYY')
ORDER BY to_char(wcs1.start_tms,'DD/MM/YYYY');

OUTPUT

Solution

Try below modified queries

select to_char(wcs1.start_tms,'DD/MM/YYYY') as dates,count(*) AS 
ecommerce
from ft_t_wcs1 wcs1, ft_t_stup stup
where stup.modl_id='ECOMMERC' and stup.CROSS_REF_ID=wcs1.acct_id and stup.end_tms is null wcs1.scenario='CREATE' and wcs1.acct_id is not null and 
wcs1.start_tms between add_months(TRUNC(SYSDATE,'mm'),-1) and 
LAST_DAY(add_months(TRUNC(SYSDATE,'mm'),-1))
group by to_char(wcs1.start_tms,'DD/MM/YYYY')
order by to_char(wcs1.start_tms,'DD/MM/YYYY');

-- Another way using JOIN clause

select to_char(wcs1.start_tms,'DD/MM/YYYY') as dates,count(*) AS 
ecommerce
from ft_t_wcs1 wcs1 
join ft_t_stup stup
ON stup.CROSS_REF_ID=wcs1.acct_id
where stup.modl_id='ECOMMERC' and stup.end_tms is null wcs1.scenario='CREATE' and wcs1.acct_id is not null and 
wcs1.start_tms between add_months(TRUNC(SYSDATE,'mm'),-1) and 
LAST_DAY(add_months(TRUNC(SYSDATE,'mm'),-1))
group by to_char(wcs1.start_tms,'DD/MM/YYYY')
order by to_char(wcs1.start_tms,'DD/MM/YYYY');