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. > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
