Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Simple subquery in Access

I have got a Subquery issue that I am sure has a really really simple solution but I can't figure out what it is!

Here's what I'm trying to do, I have two tables, let's say, customer and orders. The customer table obviously stores a list of individual customers and the orders table stores a list of orders placed by clients. I am trying to create a query that will return each customer's details as well as the total order amount placed by that customer. Try as I might I can't seem to be able to get this query to work as it says:

"You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's from clause."

I am trying to go with soemthing like this, please could anyone advise on what is wrong?

select
  customer.name,
  customer.address,
  (select sum(order.orderamount) from order, customer where order.customerid = customer.id)
from
  customer

THanks!

like image 725
user1046016 Avatar asked Jan 13 '12 15:01

user1046016


People also ask

What is a simple query in access?

A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database.

What is a subquery with example?

In SQL, it's possible to place a SQL query inside another query known as subquery. For example, SELECT * FROM Customers WHERE age = ( SELECT MIN(age) FROM Customers ); Run Code. In a subquery, the outer query's result is dependent on the result-set of the inner subquery.


1 Answers

select 
  customer.name, 
  customer.address, 
  (select sum(order.orderamount) from order where order.customerid = customer.id) as amount
from customer 

but you can do it wihout subquery:

select 
  customer.name, 
  customer.address, 
  sum(order.orderamount) 
from order 
   join customer on order.customerid = customer.id
group by   customer.name,   customer.address
like image 99
Florin stands with Ukraine Avatar answered Sep 19 '22 00:09

Florin stands with Ukraine