Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

ORACLE SQL number of customers who have ordered something on a date

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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>
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading