Ndtyjky

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

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');

It's hard to suggest an answer without understanding your table relationship, but I can tell that your problem is there is no relationship between your subquery and your main query. Your subquery simply returns a count where modl_id='ECOMMERC', so that value will always be the same - in your case, 105. You need to add a JOIN criteria to the subquery that ties the unique value to your main query. You'll also want to alias the table names differently to ensure you're joining correctly.

You are doing unnecessary joins when you just want a correlated subquery:

SELECT to_char(wcs1.start_tms,'DD/MM/YYYY') as dates,

       (SELECT count(*) 

        FROM ft_t_stup stup 

        WHERE stup.modl_id=  'ECOMMERC' AND

              stup.CROSS_REF_ID = wcs1.acct_id

              stup.end_tms IS NULL

       ) AS ecommerce

FROM ft_t_wcs1 wcs1

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');