check the column names, I may have mispelt. On Fri, Dec 12, 2008 at 1:41 PM, MW Mann <[email protected]> wrote:
> Sure, I think I got you. > > select cwd1.valid_until > ,cwd1.description > ,count(mch_code) > 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 > -- 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 -~----------~----~----~----~------~----~------~--~---
