1

i have two tables as below:

Table 1 "customer"   with fields   "Cust_id", "first_name",  "last_name"    (10 customers)
Table 2 "cust_order" with fields   "order_id", "cust_id",                   (26 orders)

I need to display "Cust_id" "first_name" "last_name" "order_id"

to where i need count of order_id group by cust_id like list total number of orders placed by each customer.

I am running below query, however, it is counting all the 26 orders and applying that 26 orders to each of the customer. 

SELECT COUNT(order_id), cus.cust_id, cus.first_name, cus.last_name
FROM cust_order, customer cus
GROUP BY cust_id;

Could you please suggest/advice what is wrong in the query?

Improve this question
3
  • Main issue is you didn't making any join between two tables in your query. Commented Dec 2, 2014 at 9:43
  • Hi Siva, I run below query with joining tables but giving same result: SELECT COUNT(order_id), cus.cust_id, cus.first_name, cus.last_name FROM cust_order JOIN customer cus GROUP BY cust_id am i doing something wrong here? Commented Dec 2, 2014 at 9:48
  • you need to say the type of join. See my answer for details on the possible joins, as well as the ON part to say how they are joined (queries don't really look at the relationships when executing unless you telll them to) Commented Dec 2, 2014 at 9:52

3 Answers 3

Reset to default
2

You issue here is that you have told the database how these two tables are 'connected', or what they should be connected by:

Have a look at this image:

enter image description here

~IMAGE SOURCE

This effectively allows you to 'join' two tables together, and use a query between them.

so you might want to use something like:

SELECT COUNT(B.order_id), A.cust_id, A.first_name, A.last_name 
FROM customer A
LEFT JOIN cust_order B     //this is using a left join, but an inner may be appropriate also
ON (A.cust_id= B.Cust_id)  //what links them together
GROUP BY A.cust_id;        // the group by clause

As per your comment requesting some further info:

Left Join (right joins are almost identical, only the other way around):

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table. ~Tutorials Point.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

LEFT joins will be used in the cases where you wish to retrieve all the data from the table in the left hand side, and only data from the right that match.

Execution Time

While the accepted answer in this case may work well in small datasets, it may however become 'heavy' in larger databases. This is because it was not actually designed for this type of operation.

This was the purpose of Joins to be introduced.

Much work in database-systems has aimed at efficient implementation of joins, because relational systems commonly call for joins, yet face difficulties in optimising their efficient execution. The problem arises because inner joins operate both commutatively and associatively. ~Wikipedia

In practice, this means that the user merely supplies the list of tables for joining and the join conditions to use, and the database system has the task of determining the most efficient way to perform the operation. A query optimizer determines how to execute a query containing joins. So, by allowing the dbms to choose the way your data is queried, you can save a lot of time.

Other Joins/Summary

  • AN INNER JOIN will return data from both tables where the keys in each table match
  • A LEFT JOIN or RIGHT JOIN will return all the rows from one table and matching data from the other table.
  • Use a join when you want to query multiple tables.
  • Joins are much faster than other ways of querying >=2 tables (speed can be seen much better on larger datasets).
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Hi jbutler483, thank you so much for the detailed explanation. it really helped to understand the joining tables concept. Really appreciated. Regards, Viku
Change the alias cus to A in your select statement.
1

You could try this one:

SELECT COUNT(cus_order.order_id), cus.cust_id, cus.first_name, cus.last_name 
FROM cust_order cus_order, customer cus 
WHERE cus_order.cust_id = cus.cust_id
GROUP BY cust_id;
Improve this answer

4 Comments

Hi Mehdi, this is working like treat, thank you so much. Regards, Viku
A possible problem here is that it will be a lot less efficient than using joins with larger datasets
Hi jbutler483, i am really sorry asking you lot of question as i am new to SQL. SO, if i understood correctly, in the large dataset, will WHERE command take lot of time to execute OR it may not display full result. i really liked the below post of yours. if this is not much hassle to you, can you please explain bit on LEFT and RIGHT JOIN? Regards, Viku
@Viku Sorry for only seeing this now (in future, much like in facebook to tag some, prefix their username with the @ symbol) but have added clarification to my post.
1

Maybe an left join will help you

SELECT COUNT(order_id), cus.cust_id, cus.first_name, cus.last_name ]
FROM customer cus 
LEFT JOIN cust_order co 
ON (co.cust_id= cus.Cust_id ) 
GROUP BY cus.cust_id;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.