Record count in a table view 1

[bob3940]

I an new to SQL and I have what I think should be a simple question but I am having no luck in figuring it out.

I have a customer table

customer_id
customer_name
etc.

and an orders table

order_id
order_number
order_id
order_customer_id
etc.

I created a view that lists all of my customers. I would like to add a column in the view to be a count of how many orders are in the orders table for that client.

I have tried adding a count(order_customer_id) into the SELECT that I created but it keeps counting all of the orders in the table not just the orders for the one customer. My view keeps looking like this:

CustomerID CustomerName Orders
1 Smith 50
2 Jones 50

when it should really be:

CustomerID CustomerName Orders
1 Smith 35
2 Jones 15

Any assistance in how I can accomplish this would be appreciated.

 

[towerbase]

Forget about VIEWs for the moment and concentrate on getting a query to produce your required results. Something like this should work (although I haven't tested it).

SELECT
  customer_id AS CustomerID
, customer_name AS CustomerName
, COUNT(*) AS Orders
FROM 
  customer
    JOIN orders
      ON customer_id = order_customer_id
GROUP BY 
  customer_id

You may want to add an ORDER BY clause.
Once you've got it working you can think about creating a VIEW.

As you are new to SQL may I suggest reading/buying Simply SQL by Rudy Limeback? Not only is this a great introductory text but Rudy is also most helpful in this (and other) forums.

Andrew
Hampshire, UK

 

[r937]

andrew, thanks for the very kind words



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon