hai , try this SELECT VALID_UNTIL,LTRIM(RTRIM(UPPER(DESCRIPTION))) DESCRIPTION , COUNT(*) FROM ifsinfo.cust_warr_detail WHERE VALID_UNTIL = TO_CHAR(SYSDATE,'RRRRMM') GROUP BY VALID_UNTIL,LTRIM(RTRIM(UPPER(DESCRIPTION)))
regards binu On Tue, Dec 16, 2008 at 9:31 AM, Jaska <[email protected]> wrote: > > 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" <[email protected]> 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 <[email protected]> > 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 <[email protected]> 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" <[email protected]> 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 <[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- 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 [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 -~----------~----~----~----~------~----~------~--~---
