I don't understand what you want- could you please give some sample data and the desired output. That will help me at very least. Thanks
On Mon, Dec 15, 2008 at 10:53 AM, Jaska <jaakko.simp...@gmail.com> wrote: > > Thanks for trying but sorry, these all calculate counts wrong. > > What I know is that a.valid_until >= b.valid_until when counting > because with that you get all machines which are under warranty on > that current month. > With a.valid_until = b.valid_until we get count of machines which > warranty ends on that month. > > And Peps, my first message/query includes also description field, and > that's why we have to use group by in main query. > And because of that this is not so easy to solve.. > > Br, > Jaska > > Ps. Moore, you are right that with dual and subqueries I can do this, > but because we have different count of descriptions and there can be > new ones and so on, I don't want make any harcoded allocations.. > > On 12 joulu, 17:01, Peps <jeffrey.mendi...@yahoo.ca> wrote: > > Hi Jaska, > > > > This is what I understand you want - For a given month of a given year > > you want to know how many machines are under warranty regardless of > > the machine description. > > > > I believe the following query will get you the result you need. > > SELECT DISTINCT c.valid_until, > > (SELECT COUNT(a.mch_code) > > FROM ifsinfo.cust_warr_detail a > > WHERE a.valid_until = c.valid_until) > > FROM ifsinfo.cust_warr_detail c; > > > > The sub-query will get you the count of rows in > > ifsinfo.cust_warr_detail for the month that was selected by the main > > query. This is achieved by "WHERE a.valid_until = c.valid_until". > > > > Also, notice the DISTINCT key word. Since there are multiple rows with > > the same month and year you want to ensure you only select a given > > month of a year one time only. I believe this is what you were trying > > to do with the groub by claus. > > > > You don't need a group by in the sub-query because the count function > > is already doing this for you. > > > > Now, I'm assuming that the column mch_code is a primary key or unique > > in the table ifsinfo.cust_warr_detail. > > > > Also, I'm basing this query on the fact that the value in the column > > valid_until is in the format of YYYYMM already. > > > > I hope this helps. > > > > On Dec 12, 6:41 am, "MW Mann" <mwm...@gmail.com> wrote: > > > > > 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 <jaakko.simp...@gmail.com> > 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" <mwm...@gmail.com> 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 <jaakko.simp...@gmail.com> > 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- Hide quoted text - > > > > > - Show quoted text - > > > > > > > -- 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 Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
