Unable to get the right output from Oracle SQL

Question

I have a table with field1, field2, field3, … and I need to count the number of items in field1 such that I return all records(field1,filed2,field3,…) that occur 6 times or less in the table.

My SQL code is:

SELECT field1, field2, field3, count(field1) CNT
FROM myTable
WHERE trunc(date)  = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
GROUP BY field1
HAVING COUNT(field1)  <  7;

The output that I am getting from the above code is all records are returned from the table not what I expected? Any help would be appreciated!!

What do you mean by "number of items in field1?" Could you please post some sample data and desired output? — Quassnoi
– Quassnoi, Commented Mar 22, 2011 at 16:58
I need to count the contents of field1 and if there or 6 or less I need to return those record for the table. — John Harris
– John Harris, Commented Mar 22, 2011 at 17:06
@John: sorry, I don't understand what does "count the contents of field1" mean. It would be really easier if you posted sample data. — Quassnoi
– Quassnoi, Commented Mar 22, 2011 at 17:08
@John: just a misambiguation: If field1 appears in 7 or more rows but trunc(date)=tp_date(‘03/22/2011’,’mm/dd/yyyy’) is true for less than 7 of them, do you want these (less than 7 rows) to appear or not? — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 23, 2011 at 0:32
Also: if field1 appears in less than 7 rows but trunc(date)=tp_date(‘03/22/2011’,’mm/dd/yyyy’) is true for a few of them, do you want all of them to appear or not? I guess no in this case. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 23, 2011 at 0:34

Lost in Alabama · Accepted Answer · 2011-03-22 17:23:14Z

1

I think you need to use a subquery:

SELECT field1, field2, field3, 
FROM myTable
WHERE trunc(date)  = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
AND field1 in 
   (SELECT field1
    FROM mytable
    GROUP BY field1
    HAVING COUNT(field1)  <  7);

answered Mar 22, 2011 at 17:23

Lost in Alabama

1,65310 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Harris Over a year ago

Lost in Alabama, You code is spot on!! This solved my issue and now I know to use subquery, very powerful. P.S. I am originally from Alabama but now Lost in California! Thank John

Lost in Alabama Over a year ago

You're welcome. Subqueries certainly can give you many more options for solving sql problems. Please mark the answer as accepted.

Lost in Alabama Over a year ago

P.S. Wish I lived in California too!

John Harris Over a year ago

Sorry but this is the first time I used Stackoverflow and I do not see where to mark the answer as accepted?

clyc · Accepted Answer · 2011-03-22 23:57:54Z

1

WITH tmp AS
(
    SELECT field1, COUNT(1) as CountOfField1
    FROM myTable
    WHERE trunc(date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
    GROUP BY field1
    HAVING COUNT(field1) < 7
)
SELECT  mytable.field1, mytable.field2, mytable.field3, tmp.CountOfField1
FROM    myTable 
        INNER JOIN tmp 
            ON myTable.Field1 = tmp.Field1

edited Mar 22, 2011 at 23:57

answered Mar 22, 2011 at 17:30

clyc

2,45014 silver badges15 bronze badges

2 Comments

John Harris Over a year ago

Clyc, Thanks you for your example! I will code this up to see how this works!

ypercubeᵀᴹ Over a year ago

You don't need the <7 check twice.

ypercubeᵀᴹ · Accepted Answer · 2011-03-23 00:29:09Z

0

Yet another way to do it:

SELECT t.field1, t.field2, t.field3, 
FROM myTable t
WHERE trunc(t.date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
  AND EXISTS
  ( SELECT *
    FROM mytable t2
    WHERE t2.field1 = t.field1
      AND trunc(t2.date) = tp_date(‘03/22/2011’,’mm/dd/yyyy’)
    GROUP BY t2.field1
    HAVING COUNT(t2.field1)  <  7
  )
;

edited Mar 23, 2011 at 0:29

answered Mar 22, 2011 at 23:46

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Collectives™ on Stack Overflow

Unable to get the right output from Oracle SQL

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related