I have 2 tables involved, the "customers" table and the "orders" table.
The "customers" table has the following columns:
Customerid, companyname, contactname, contactittle, addresses, city
The "Orders" table has the following columns:
orderid, customerid, employeeid, orderdate, shippeddate, shipname
I have to do where the number of customers who have ordered a product in the year 97 comes out.
It should be something like
Number of clients 20
I have tried the following code, but I think it is wrong for the following two reasons
1-I think that like '%97%' may cause errors in the future
2-For some reason it takes me the number of orders that have been made, not the number of customers
SELECT DISTINCT(COUNT (companyname))as Numero_CLIENTES
FROM customers JOIN orders ON customers.customerid=orders.customerid
WHERE orderdate like '%97%'
>Solution :
Sample data:
SQL> with
2 customers (customerid) as
3 (select 1 from dual union all
4 select 2 from dual union all
5 select 3 from dual
6 ),
7 orders (orderid, customerid, orderdate) as
8 (select 100, 1, date '1997-12-02' from dual union all --> customer 1 counts as "1"
9 select 101, 1, date '2000-03-28' from dual union all
10 select 102, 2, date '1997-05-29' from dual union all --> customer 2 counts as "1" as well
11 select 103, 2, date '1997-06-12' from dual --> because both orders were made in 1997
12 )
Query:
13 select count(distinct c.customerid) numero_clientes
14 from customers c join orders o on o.customerid = c.customerid
15 where extract(year from o.orderdate) = 1997;
NUMERO_CLIENTES
---------------
2
SQL>