Re: need some SQL help

On Fri, 23 Dec 2005 18:45:55 GMT, howzat wrote:

>Hi all
>
>I'm having some trouble figuring this out so I'd appreciate if someone could
>point me in the right direction. (simplified schema appears below)
>
>I have an orders table which holds information about the type of order
>(phone, web, retail etc) and the amount of the order. The orders table is
>linked to a customers table via a customer_id column.
>
>I need to run a query which will return customers who match multiple order
>types and values e.g. all customers who spent more than 200 via mail order
>and more than 100 on the web
>
>The only way I've been able to do this is using subqueries, one for each
>order_type+amount condition. This works great on a small database but the
>live database will have about 30000 rows in the orders table and users will
>want to run a query on multiple order_type+amount conditions.

Hi David,

The following is untested since I don't have MySQL. It *should* work on
all ANSI-compliant databases. It should also take no more than one
single pass over the data in the table (if the optimizer in your
database is worth it's salt, that is).

SELECT customer_id
FROM (SELECT customer_id, order_type
FROM orders
WHERE order_type IN (3, 4)
GROUP BY customer_id, order_type
HAVING ( order_type = 3 AND SUM(amount) > 100)
OR ( order_type = 4 AND SUM(amount) > 200) ) AS derived
GROUP BY customer_id
HAVING COUNT(*) = 2


Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.

Relevant Pages

  • RE: Store Added Value List Items
    ... You could even union that query with the actual list from the form ... Each user in this database needs to have access to all the same customers. ... is praticle to create a seperate one field table to store values for a lookup ...
    (microsoft.public.access.formscoding)
  • Re: Vulnerability in Microsoft Jet Database Engine Could Allow Rem
    ... Are the joined tables in your query remote tables ... on a test machine you might try compacting the database after ... customers who stopped working were running Windows XP Pro. ... after the service packs were applied, either Windows XP Pro/Home Service Pack ...
    (microsoft.public.access.security)
  • Re: Creating a query from multiple identical tables
    ... >I am trying to create a query from multiple tables with the same number of ... > job we have in progress with each table representing one of our customers. ... > I'd like to know how I can create a query to pull form each customers ... > to recreate the database in a while if there is a better way to organise ...
    (microsoft.public.access.queries)
  • Re: How to convert records in expression fields in Forms to a tabl
    ... When I double click the Query, the Access gave an error: ... engine can't find input table or query "Customers". ... opportunity to review the database again and again to understand more ... open it in design view to ...
    (microsoft.public.access.forms)
  • Re: help in querying tables
    ... I managed to create a query which brought back only those who were ... suppliers / customers and which were active. ... You need to be asking this in a database newsgroup, where the database experts hang out. ...
    (comp.lang.php)