result set

rajesh_css · February 18th, 2009, 06:39 AM

Hi,

I have a table X with 3 columns name ,date(YYYYMMDD),total

name--date--total

x---02Jan2009--1
y---06Jan2009--1
z---10Jan2009--1
x---01Oct2008--1
x---03Nov2008--1
y---22Dec2008--1
x---02Feb2009--1
y---22Feb2009--1
z---03Feb2009--1
z---04Feb2009--1

select Name,count(total) from X where
TO_CHAR(date,'YYYYMM') = (TO_CHAR(TO_DATE('20090201','YYYYMMDD'),'YYYYMM'))
Group BY Name

i will get the result as

Name--Month total
x--1
y--1
z--2

i need to write a query in such a way that i need to get the result for that year which is in between 200901 to 200902 (the count of the selectee month and the previous months of the year)

Name--month total--year total

x--1--2
y--1--2
z--2--3

Regards
Raj

happygv · May 18th, 2009, 08:41 AM

Hi rajesh_css,

I am not sure which DBMS you use, and what you actually wanted in this case. Just giving a try to see if this helps.

Code:

select Name,
SUM( (SELECT count(total) from X T1 where TO_CHAR(T1.date,'YYYYMM') = (TO_CHAR(TO_DATE('20090201','YYYYMMDD'),'YYYYMM')) AND T2.Name = X.Name ) )AS MonthTotal,
SUM( (SELECT count(total) from X T2 where TO_CHAR(T2.date,'YYYY') = (TO_CHAR(TO_DATE('20090201','YYYYMMDD'),'YYYY')) AND T2.Name = X.Name ) ) AS YearTotal
FROM X
Group BY Name

Thanks,

Old Pedant · May 18th, 2009, 03:33 PM

You should almost always avoid changing dates into strings! Try to work with dates *AS DATES* and if you can't do that then work with them as numbers.

What DB is this?? Probably Oracle?

So see if Oracle doesn't have a better way to do this.

For example:

Code:

SELECT ...
WHERE T1.date >= TO_DATE('20090101') AND T1.date < TO_DATE('20090301') 
...

See that? >= January 1st but < March 1st. Now it works even if T1.date includes both date and time.