select name, COUNT(*) from two different tables as a query result

Question

CREATE TABLE    CUSTOMER (
cusID   VARCHAR(10) NOT NULL,
cusNAME     VARCHAR(50),
CONSTRAINT CUSTOMER_PKEY PRIMARY KEY (cusID)
);

CREATE TABLE    ORDERS (
cusID       VARCHAR(10) NOT NULL,
item    VARCHAR(25),
PPRICE  NUMBER(12,2),
CONSTRAINT ORDERS_FKEY FOREIGN KEY (cusID) REFERENCES CUSTOMER(cusID)
);

i have this two tables, and i want a query results of

CUSNAME    ITEM BOUGHT
JOHN          2
MAY           5

is it possible to do this? something like select cusNAME,COUNT(*) FROM CUSTOMER,ORDERS .......and so on... have no idea how to continue this.

Is "bought" defined as the number of rows in the orders table? Or is there a quantity purchased amount somewhere? — David Aldridge
– David Aldridge, Commented May 5, 2013 at 11:25
bought is just how many times cusID appear in the orders table because customer can only buy one item at a time. so i need to find out how many item has they bought — user2351750
– user2351750, Commented May 5, 2013 at 11:33

John Woo · Accepted Answer · 2013-05-05 11:26:24Z

3

SELECT  cus.cusName,
        COUNT(*) AS ItemBought
FROM    Customer AS cus
        INNER JOIN Orders AS ord
            ON cus.CusID = ord.cusID
GROUP   BY cus.cusName

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

The query above will only show when a customer has atleast one record on table Order because it uses INNER JOIN. If you want to show all customers whether it has atleast one record on table order or not, use LEFT JOIN instead.

answered May 5, 2013 at 11:26

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Woo Over a year ago

@downvoter you have so many chances to downvote my answers. clearly :) happy downvoting.

Collectives™ on Stack Overflow

select name, COUNT(*) from two different tables as a query result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related