Re: SQL Problem Group by

Arun,

In the having clause, change the equal sign (=) to a less than (<) sign. I'm appending an example. You'll see in the example:

1. The first query/select returns 22 rows, while the second returns 21 rows.
2. The row with the largest count is NOT in the results of the second query/select.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

12:53:57 By your command, human>
select lastdate, count(*)
  from REMA
 where status=1
 group by lastdate;

LASTDATE COUNT(*)

-------------------- ----------
08-Nov-2002 00:00:00       1409
09-Nov-2002 00:00:00         13
10-Nov-2002 00:00:00          5
11-Nov-2002 00:00:00         29
12-Nov-2002 00:00:00         17
13-Nov-2002 00:00:00         15
14-Nov-2002 00:00:00          8
15-Nov-2002 00:00:00         35
16-Nov-2002 00:00:00         15
17-Nov-2002 00:00:00         15
18-Nov-2002 00:00:00         25
19-Nov-2002 00:00:00         32
20-Nov-2002 00:00:00         28
21-Nov-2002 00:00:00         26
22-Nov-2002 00:00:00         27
23-Nov-2002 00:00:00         24
24-Nov-2002 00:00:00          1
25-Nov-2002 00:00:00         15
26-Nov-2002 00:00:00         16
27-Nov-2002 00:00:00         14
28-Nov-2002 00:00:00         18
29-Nov-2002 00:00:00          3

22 rows selected.

Elapsed: 00:00:00.00
12:55:29 By your command, human>
select lastdate, count(*)
  from REMA
 where status=1
 group by lastdate
having(count(*))<(select max(count(*))

                    from REMA
                   where status=1
                   group by lastdate);

LASTDATE               COUNT(*)
-------------------- ----------
09-Nov-2002 00:00:00         13
10-Nov-2002 00:00:00          5
11-Nov-2002 00:00:00         29
12-Nov-2002 00:00:00         17
13-Nov-2002 00:00:00         15
14-Nov-2002 00:00:00          8
15-Nov-2002 00:00:00         35
16-Nov-2002 00:00:00         15
17-Nov-2002 00:00:00         15
18-Nov-2002 00:00:00         25
19-Nov-2002 00:00:00         32
20-Nov-2002 00:00:00         28
21-Nov-2002 00:00:00         26
22-Nov-2002 00:00:00         27
23-Nov-2002 00:00:00         24
24-Nov-2002 00:00:00          1
25-Nov-2002 00:00:00         15
26-Nov-2002 00:00:00         16
27-Nov-2002 00:00:00         14
28-Nov-2002 00:00:00         18
29-Nov-2002 00:00:00          3

21 rows selected.

Elapsed: 00:00:00.00

"Arun Goel" <goel_ar1_at_hotmail.com> wrote in message news:asb0ai$p8f78$1_at_ID-127607.news.dfncis.de...
> Hi all,
>
> I am facing problem in getting the desired result.
>
> Here is my problem..
> my table look like
> ----->temp(A,B,C);
> Data is as follows:
> A B C
> -- -- --
> A1 B1 C1
> A1 B1 C1
> A1 B1 C1
> A1 B1 C2
> A2 B2 C1
> A2 B2 C1
> A2 B2 C2
>
> I need is the value of A, B,C for which there are maximum rows.
> It means I just need is A1,B1,C1 & A2,B2,C1 only.
>
> I tried this query
> select A,B,C,count(*)
> from temp
> group by A,B,C;
>
> It gives me this as expected
> A B C COUNT(*)
> -- -- -- ----------
> A1 B1 C1 3
> A1 B1 C2 1
> A2 B2 C1 2
> A2 B2 C2 1
>
> I don't how to filter out the rows with maximum occurences.
>
> When I use "Having" in group as follows:
>
> select A,B,C,count(*)
> 2 from temp
> 3 group by A,B,C
> 4 Having(count(*))=(
> 5 select max(count(*))
> 6 from temp
> 7 group by A,B,C
> 8 );
> A B C COUNT(*)
> -- -- -- ----------
> A1 B1 C1 3
>
>
> Then I don't get the A2,B2,C1 at all as evident from the result.
>
> Can anyone suggest me how can I get thje desired tuples.
>
> Regards
> --
> ----------------
> Arun Goel
> http://www.crml.uab.edu/~ag
>
>
>
Received on Sat Nov 30 2002 - 13:04:17 CST