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.
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
