Re: SQL Problem Group by

From: Arun Goel <goel_ar1_at_hotmail.com>
Date: Sat, 30 Nov 2002 14:18:18 -0600
Message-ID: <asb6ec$opneo$1@ID-127607.news.dfncis.de>

Hi Bertram,

But that does not serve my purpose.
See If I have data like this
A1 B1 C1
A1 B1 C1
A1 B1 C1
A1 B1 C2
A2 B2 C1
A2 B2 C1
A2 B2 C2

*********I don't need this output************* A B C COUNT(*)

-- -- ---------- A1 B1 C1 3

What I need is ..:
A1 B1 C1
A2 B2 C1.

So group of A1 & B1 should be treated entirely separately.

Thanks
Arun

--
----------------
Arun Goel
http://www.crml.uab.edu/~ag
"Bert Bear" <bertbear_at_NOSPAMbertbear.net> wrote in message
news:RY7G9.755$Sx4.646403270_at_newssvr30.news.prodigy.com...


> 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 - 14:18:18 CST