select * from ifsinfo.cust_warr_detail (primary key = mch_code) result:
Mch_Code Valid_Until Description Machine1 200811 Card/Note terminal Machine2 200906 Card/Note terminal Machine3 200905 Site Controller Machine4 200905 Double pump Machine5 200811 Multi pump Machine6 200811 Card terminal Machine7 200904 Card/Note terminal Machine8 200811 Card terminal Machine9 200906 Card/Note terminal Machine10 200810 Multi pump Machine11 200811 Multi pump Machine12 200811 Multi pump Machine13 200811 Multi pump Machine14 200811 Multi pump Machine15 200811 Double pump Machine16 200811 Single pump Machine17 200811 Single pump .... There are thousands of lines so when you group by with valid_until and description you will get almost all descriptions for all months so don't bother how to get all months to list. What I want is list of months and descriptions and count of machines which are under warranty on that month. So couple of lines could be: Valid_until Description Machines under warranty on current month 200810 Single pump 10 200810 Multi pump 7 200810 Site Controller 2 200811 Single pump 6 200811 Multi pump 5 200811 Site Controller 2 ... Valid_until -value is month what is last month when machine is under warranty. Machine should be counted on this month and all months before that. Hopefully this helps and thanks one more time! BR, Jaska On 15 joulu, 15:19, "MW Mann" <mwm...@gmail.com> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
