You might want to try 2 things because I think that you have managed to confuse everyone with your slightly different ways of describing the problem.
1) give an example of what is in the table .. just a few rows would be sufficient 2) give an example of what you expect the output to look like based on the rows of data that you have provided. I know it probably sounds like a silly thing to ask but it is often harder to clearly describe a problem in words than to show a concrete example. I do this for end-users all the time when creating reports .. just give them a piece of paper and get them to show me what it should look like given a certain set of data. Just for instance, you are not at all clear about whether 1) there is a valid_from and valid_to column.. or 2)if the valid_to column is filled in with one row for each month in which the warranty is valid for a particular machine or 3)if there is one row per machine with a valid_to column and the valid_from is merely implied. Your original question seemed to imply that case #2 was the case but your subsequent answers have left some ambiguity as to what the structure of your data actually is. so ... examples of input and output would clear this up in no time flat I am sure. We have some very smart folks posting on here and in general when they are stumped so profoundly it is due to an unclear problem statement. (you are not alone .. i am infamous in my office for fuzzy problem statements) Rob On Dec 15, 3: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 - > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
