Sure, I think I got you.
select cwd1.valid_until
,cwd1.description
,count(1)
from cust_warr_detail cwd1,
cust_warr_detail cwd2
where cwd1.valid_until <= cwd2.valid_until
group by cwd1.valid_until,cwd1.description
order by cwd1.valid_until;
Does that solve your problem?
On Fri, Dec 12, 2008 at 10:05 AM, Jaska <[email protected]> wrote:
>
> Your SQL will count only machine where valid_until is same than
> machine's valid_until.
> What I need/want is list of year/month and how many machines have
> warranty on that month.
>
> If machine have warranty 200810-200910, it should be counted every
> month on that period.
>
>
> That's why my script had that valid_until >= valid_until -condition.
>
>
> And sorry, if first message was little bit hazy. :)
>
> BR,
> Jaska
>
>
> On 12 joulu, 09:04, "MW Mann" <[email protected]> wrote:
> > You don't need a " group by a.mch_code" in your sub query. Because
> > "count(a.mch_code)" is the only column in that sub query. IE your are not
> > grouping by anything.
> > I don't understand your question fully though, what are you trying to
> > achieve with the date (valid_until) condition in your where clause.
> > However maybe these will help you.
> >
> > If you want a list of how many machines are under warrantee for every
> month:
> >
> > select valid_until,
> > description,
> > count(mch_code)
> > from ifsinfo.cust_warr_detail
> > group by valid_until,
> > description;
> >
> > If you want only for a certain month, you would add it to the where
> clause:
> >
> > select valid_until,
> > description,
> > count(mch_code)
> > from ifsinfo.cust_warr_detail
> > where valid_until = '200812'
> > group by valid_until,
> > description;
> >
> > If this doesn't answer your question, please post an example of the
> output
> > you require and the table definition and some sample data.
> >
> > --
> > Mike
> >
> > On Fri, Dec 12, 2008 at 7:59 AM, Jaska <[email protected]> wrote:
> >
> > > Hi.
> >
> > > I tried to make list how many machine (mch_code) is under warranty in
> > > each month.
> > > Valid_until is like YYYYMM. Description is type of machine.
> >
> > > select c.valid_until,
> > > c.description,
> > > (select count(a.mch_code) from ifsinfo.cust_warr_detail
> a
> > > where a.valid_until >= c.valid_until and
> > > a.description=c.description
> > > group by a.mch_code)
> >
> > > from ifsinfo.cust_warr_detail c
> >
> > > group by c.valid_until,
> > > c.description
> >
> > > This query gives error "Not group by expression".
> >
> > > I have tried to add whole subquery to main Group by section but it
> > > didn't solve this..
> > > System gives error: "Subquery expressions not allowed here".
> >
> > > Do someone knows what kind of SQL query is should use?
> >
> > > Thanks!
> >
> > > Ps.
> > > Also one working solution is that machine is listed every "month" if
> > > machine is under warranty on that month.
> > > So I can group/count these in Excel with pivot.
> >
> >
> >
>
--
Michael Mann
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
