Home » SQL & PL/SQL » SQL & PL/SQL » struck up with UNION
icon9.gif  struck up with UNION [message #267302] Thu, 13 September 2007 00:18 Go to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
Hi,

struck up with a Query using Union my requirement is as follows.
The query should fetch the sales total for this week and last week in a single row group by the department and the date of this week.
Ex:  DATE   Department   This_week_sales     Last_week_sales
  21-JUL-07  ALDO         300                 100


In the above 100 corresponds to the sales of Last week i.e 14-JUL-07

I wrote a query with union like this
select sum(this_week),sum(lasst_week),department,date from 
(select sum(sales)This_week,
       0 Last_week,
       date,
       department
from tran_data_history
where tran_date between :begin_week and :end_week
union
select 0 This_week,
       sum(sales) Last_week,
       date,
       department
from tran_data_history
where tran_date between :last_week_begin and :last_week_end)
group by
date,
department

the results came to be

THIS_WEEK      LAST_WEEK   DEPARTMENT        DATE
225	         0	 ALDO                     25-JUL-07
0	      5.66	 ALDO		         17-JUL-07
0	    1 233.7       ALDO		         18-JUL-07
0	        67        ALDO		         17-JUL-07
44.94	        0        La Senza		  22-JUL-07
694.5	      0	        ALDO		         26-JUL-07
0	     2311.5       La Senza		18-JUL-07
37.75	  	 0        La Senza		 25-JUL-07

But I want the results to be grouped by only department and date of this week
i.e
lets consider ALSO dept 25-jul-07
I want the row to be
225    1233.7    ALDO   25-jul-07


1233.7 is exactly a week back sales.. i.e 18-jul-07
I dont want that in a separate col..
Please tell me how can I proceed with this qry

Thanks in adv.
Suma
[EDITED by LF - added [code] tags]

[Updated on: Thu, 13 September 2007 13:12] by Moderator

Report message to a moderator

Re: struck up with UNION ..pl help [message #267307 is a reply to message #267302] Thu, 13 September 2007 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Please tell me how can I proceed with this qry
You should actually read & FOLLOW the posting guidelines as stated in the #1 STICKY post at top of this forum.
Re: struck up with UNION ..pl help [message #267333 is a reply to message #267302] Thu, 13 September 2007 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: struck up with UNION ..pl help [message #267388 is a reply to message #267302] Thu, 13 September 2007 03:08 Go to previous messageGo to next message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
sorry..
seeing replies I tried to edit the sql using the tools//
but could not get the same
I will remove the message from the forum.

suma

[mod-edit] removed illiterate IM speak words

[Updated on: Thu, 13 September 2007 13:13] by Moderator

Report message to a moderator

Re: struck up with UNION ..pl help [message #267394 is a reply to message #267388] Thu, 13 September 2007 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't remove it! There's no point in doing that.

I've added the [code] tags for you. Next time, please, do it yourself (also, read the OraFAQ Forum Guide prior to posting your next message here).
Re: struck up with UNION ..pl help [message #267400 is a reply to message #267394] Thu, 13 September 2007 03:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Are you sure you've posted the exact query that you're using?
I ask because this fragment :
select sum(sales)This_week,
       0 Last_week,
       date,
       department
from tran_data_history
where tran_date between :begin_week and :end_week
won't work (and neither will the other half of the UNION.
You'd need to add a GROUP BY clause to get an executable query out of that.

Also, you seem to be referencing two different date columns, one of which is called DATE - is this correct?

Can you cut and paste (ie not type in by hand) your actual query?

Assuming that you have a working query and these are the results it is producing, I'd suspect that the problem is that some of the dates have a timestamp, and some don't.
In the outer query, try doing a TRUNC on the dates.
Re: struck up with UNION ..pl help [message #267407 is a reply to message #267302] Thu, 13 September 2007 03:54 Go to previous message
suma_orkl
Messages: 16
Registered: July 2007
Junior Member
Hi
Thank for the response
yes I did not give the entire query I used I abridged it..since it has many joins and table inside it...

My issue now got resolved...


thank
suma

[mod-edit] removed illiterate IM speak words

[Updated on: Thu, 13 September 2007 13:12] by Moderator

Report message to a moderator

Previous Topic: Out of present range
Next Topic: cursor query
Goto Forum:
  


Current Time: Thu Dec 08 14:12:06 CST 2016

Total time taken to generate the page: 0.08940 seconds