I am trying to get the number of sold subscriptions from one table and the store name from another. The tables have a common column containing a store id, however the columns do not have the same name.

Talbe1                  |   Table2

Amount     store_code   |   store_name    store_no

1          nf1ii        |   Jims kiosk    nf1ii
1          nfoi1        |   Henrys kiosk  nfoi1
...        ...          |   ...           ...

I am trying to count the number of subscriptions sold for each store name. How is this doen in Oracle? I am fairly new to SQL in general so please forgive me if the question is phrased strangely.


Here's a statement that should work and will also display the store when there are no subscriptions for that store in table2.

SELECT t2.store_no,
       NVL(total_subs, 0) total_subs
  FROM Table2 t2,
       (SELECT store_code,
               COUNT(*) total_subs
          FROM table1
         WHERE subs = 'twin'
           AND sdate > TO_DATE('01.05.13','MM.DD.RR')
         GROUP BY store_code
       ) t1
 WHERE t2.store_no=t1.store_code (+)

It's also good practice when you're wanting to compare dates that you perform the date conversion explicitly, and don't rely on Oracle to do it for you. So in my example I used the Oracle TO_DATE function to make "01.05.13" into a date. In your example the only way Oracle would know what format the date is in is if the NLS_DATE_FORMAT session variable is set to MM.DD.RR or DD.MM.RR. If this session variable was changed, or you ran your code on a different Oracle instance whose NLS_DATE_FORMAT is different, then your code would fail. I also changed the column name in my example to sdate since normally the word date can't be used as a column name unless it's enclosed in quotes (i.e. "date").

  • Thank you i tried your code (and added trim() around the last statement because store_code had a trailing space in it.) worked fine :) – Ole Henrik Skogstrøm Jun 11 '13 at 12:52
,sum (t1.Amount) -- if Amount can be >1
,count (t1.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table1 t1
right join Table2 t2 on t1.store_code=t2.store_code
t1.subs = 'twin' /* type of subscription */
and t1.date > '01.05.13'
group by t2.store_name, t1.store_code

It is a good practice to use LEFT JOIN rather then RIGHT JOIN, as the later one is harder to imagine and understand.

,sum (t2.Amount) -- if Amount can be >1
,count (t2.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table2 t1
left join Table1 t2 on t1.store_code=t2.store_code
t2.subs = 'twin' /* type of subscription */
and t2.date > '01.05.13'
group by t1.store_name, t2.store_code
  • Hi, Thank you for your answer. It was really usefull, we got it working at last. I think the problem with my original code was mainly that the store_code column had a trailing space in it. – Ole Henrik Skogstrøm Jun 11 '13 at 10:20

Working code i ended up using was:

select count(*) as Amount, d.store_name
from table1 sa
full outer join table2 d on trim( sa.store_code )=d.store_no
sa.soc = 'TWIN'
and sa.date > '01.05.2013'
group by d.store_name
order by Amount desc;

the column store_code gave me some trouble due to trailing spaces in that column and not in the store_no column. This caused the match to fail.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.