I obviously didnt explain myself very clearly ...

what would be useful is to show .. for instance.. given a couple of rows 
of data (like you did) exactly what you would expect the output to be...

To say that it "could" look like something isnt very helpful. Tell us 
exactly what you think the output should be given a set of input.

I know that I am sounding like a smartass but I really am not. Your 
examples seem to be confusing people even more than not having an 
example and honestly we really are a pretty smart bunch. ... well most 
days anyway  :-)

A good example is almost always the best way to explain a problem.

Rob

Jaska 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" <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
-~----------~----~----~----~------~----~------~--~---

Reply via email to