Home » SQL & PL/SQL » SQL & PL/SQL » Please hepl me on this QUERY
Please hepl me on this QUERY [message #240113] Thu, 24 May 2007 03:00 Go to next message
kinkin20
Messages: 6
Registered: May 2007
Junior Member
I have 2 table
Table Customer
customer_key
state
region

and Table Purchase
customer_key
purchase_price

Query: what state did customers spend the most?


Please help me by email
thank a lot
Re: Please hepl me on this QUERY [message #240118 is a reply to message #240113] Thu, 24 May 2007 03:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
kinkin20 wrote on Thu, 24 May 2007 10:00
Please help me by email
I don't think you understand the purpose of a forum. Ask public questions, get public replies. If you want a private session, I could get you in touch with my company. We have reasonable fees Wink.

MHE
Re: Please hepl me on this QUERY [message #240121 is a reply to message #240118] Thu, 24 May 2007 03:20 Go to previous messageGo to next message
kinkin20
Messages: 6
Registered: May 2007
Junior Member
Sorry this is a first time i post mess in the forum .i don't know the style of forum.

on this query : i already get the State and Purchase_price
I don't know how to get the Max value.

SELECT c.state,sum(p.purchase_price)
FROM customer c,purchases p
where c.region = 'N'
or c.region = 'S'
group by c.state
Having p.purchase_price = (SELECT MAX(SUM(p.purchase_price))
FROM PURCHASES p
GROUP BY c.state)

Please help me, tomorow i have to submit it for my teacher

thank a lot
Re: Please hepl me on this QUERY [message #240122 is a reply to message #240118] Thu, 24 May 2007 03:24 Go to previous messageGo to next message
Snowblitzz
Messages: 8
Registered: May 2007
Junior Member
try to make a query with the 2 tables (join them on customer_key
and add

and purchase_price = (select max(purchase_price) from Purchase)

Added: Seems like you already answerd your own question in the query above.

[Updated on: Thu, 24 May 2007 03:25]

Report message to a moderator

Re: Please hepl me on this QUERY [message #240129 is a reply to message #240122] Thu, 24 May 2007 03:48 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The old fashioned way would to sort the groups by their price, wrap it in an inner select and take the first record:
SELECT vw.state
     , vw.tot
FROM ( SELECT c.state
            , SUM(p.price) tot
       FROM   customers c
          ,   purchases p
       WHERE  p.cust_id = c.cust_id
       GROUP  BY c.state
       ORDER  BY tot DESC
     ) vw
WHERE rownum = 1
/


MHE
Re: Please hepl me on this QUERY [message #240140 is a reply to message #240129] Thu, 24 May 2007 04:23 Go to previous message
kinkin20
Messages: 6
Registered: May 2007
Junior Member
Thank you
I successfully got the value of the best purchase
thank everybody
Previous Topic: problem with escape character
Next Topic: DBMS_OUTPUT.PUT_LINE
Goto Forum:
  


Current Time: Sat Dec 10 16:30:40 CST 2016

Total time taken to generate the page: 0.08228 seconds