Home » SQL & PL/SQL » SQL & PL/SQL » Query question
Query question [message #184219] Tue, 25 July 2006 14:47 Go to next message
jbeals
Messages: 4
Registered: July 2006
Junior Member
As a simplistic example, I have:

select a.customer_name, b.total_sales
from Customers a,
sales b
where sales_date = thismonth

UNION

select a.customer_name, b.total_sales
from Customers a,
sales b
where sales_date = lastmonth

UNION

select a.customer_name, b.total_sales
from Customers a,
sales b
where sales_date = twomonthsago

I get the right information back, but I need to sort descending by the sales from last month. What's the best way to construct my query so that I get:

select a.customer_name, (select b.total_sales from sales b where sales_date = thismonth) CurrentSales,
(select b.total_sales from sales b where sales_date = lastMonth) lastMonthSales,
(Select b.total_sales from sales b where sales_date = twomonthsago
from Customers a,
sales b

so that I can order by LastMonthSales desc?

Thanks in advance!


Re: Query question [message #184285 is a reply to message #184219] Wed, 26 July 2006 00:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Don't you need to join sales with customers here? You also might want to consider UNION ALL instead of UNION in your first select.

Something like this might work:
SELECT a.customer_name,
       s1.total_sales currentsales,
       s2.total_sales lastmonthsales,
       s3.total_sales pastsales
FROM   customers a, sales s1, sales s2, sales s3
WHERE  a.customer_id = s1.customer_id
AND    a.customer_id = s2.customer_id
AND    a.customer_id = s3.customer_id
AND    s1.sales_date = thismonth
AND    s2.sales_date = lastmonth
AND    s3.sales_date = twomonthsago
ORDER BY s2.total_sales


Can it be this simple or am I missing something?

MHE

[Updated on: Wed, 26 July 2006 00:53]

Report message to a moderator

Re: Query question [message #184314 is a reply to message #184285] Wed, 26 July 2006 02:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or something like:

SELECT a.customer_name,
       SUM(DECODE(s1.sales_date,thismonth, total_sales,0)) currentsales,
       SUM(DECODE(s1.sales_date,lastmonth, total_sales,0)) lastmonthsales,
       SUM(DECODE(s1.sales_date,twomonthsago, total_sales,0)) pastsales
FROM   customers a, sales s1
WHERE  a.customer_id = s1.customer_id
AND    s1.sales_date IN ( thismonth,lastmonth,twomonthsago)


Maaher has a point - joins are frequently a good idea in this sort of situation.
Re: Query question [message #184420 is a reply to message #184219] Wed, 26 July 2006 08:59 Go to previous messageGo to next message
jbeals
Messages: 4
Registered: July 2006
Junior Member
I guess I left out a little bit of pertinent info. Embarassed The SQL itself was embedded in Reports 2.5, and some joins were being performed there. Like I said, it was just an off the cuff example for simplicity's sake. The actual 'total sales' is the result from 9 tables which have been joined in the actual queries.

Thanks to all who replied though! I think it was enough to get me out of the place I was in, and pointed in the right direction. Occasionally I get that continuous loop going in my head and need a swift kick to get on a new track, or I just keep making the same mistakes over and over.

Thanks again, everyone!

Re: Query question [message #184454 is a reply to message #184420] Wed, 26 July 2006 10:41 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"JBeals" - does it stand for Jennifer Beals? If so, aren't your supposed to dance in Flashdance instead of writing reports? Wink
Previous Topic: Which Oracle Release Do You Use
Next Topic: Can values of rows being queried dynamically altered even as they are queried?
Goto Forum:
  


Current Time: Tue Dec 06 02:32:07 CST 2016

Total time taken to generate the page: 0.09458 seconds