0

I am trying to build where clause condition on table having columns “Id”, itemNumber” which can be either 1 or 2 for any row and “date”.

My goal is to write where clause such that i only get “Id’s” where “itemNumber” is 2, and then if count is greater than some value it should filter whole rows to date between today and today+1, otherwise today and today+2.

I tried,

Select Id  
from table 
where itemNumber=2 And ((count(itemNumber)>2 and date between ‘today’ and ‘today+1’) OR (count(itemNumber)<=2 and date between ‘today’ and ‘today+2’))

I got error saying you need to have sql “having”. Am i doing it wrong?

PM 77-1
  • 12,933
  • 21
  • 68
  • 111
vamsikrishna mannam
  • 105
  • 2
  • 9
  • You use aggregate functions. `WHERE` is for pre-aggregation conditions, `HAVING` - for post-aggregation. Since results of `COUNT` are known only after aggregation is completed - you need `HAVING`. – PM 77-1 Nov 06 '17 at 23:54
  • Possible duplicate of [SQL - having VS where](https://stackoverflow.com/questions/9253244/sql-having-vs-where) – PM 77-1 Nov 06 '17 at 23:55
  • Learn about `GROUP BY` here https://www.techonthenet.com/sql/group_by.php – Eric Nov 07 '17 at 00:02

1 Answers1

0

Try it like this:

SELECT id
FROM t
WHERE itemNumber = 2
GROUP BY id
HAVING (COUNT(itemNumber) > 2 AND date BETWEEN 'today' and 'today+1'))
OR (COUNT(itemNumber) <= 2 AND date BETWEEN 'today' and 'today+2'))

Think of HAVING as a WHERE clause after you have grouped your data, which you have to do if you want to count something by group (or id).

kjmerf
  • 4,275
  • 3
  • 21
  • 29
  • I think I phrased my question wrong. The column “Id” is primary key, but i am having one more column “Id1” which can be duplicated. So, my goal is to select all “Id”, with where condition what i mentioned. Since i am selecting “Id”, i have to use that column in “group by” and my rows are not grouping since there can be only one row for one Id as it is primary key. So having all conditions are becoming useless. – vamsikrishna mannam Nov 07 '17 at 02:34
  • @vamsikrishnamannam sounds like something we can resolve. Some sample data would help, though, to understand exactly what you mean – kjmerf Nov 07 '17 at 10:07
  • ID ID1 itemNumber date 1 1 1 date 2 2 2 date 3 2 1 date 4 2 2 date – vamsikrishna mannam Nov 07 '17 at 13:23
  • ID(1,2,3,4), ID1(1,2,2,2), itemNumber(1,2,1,2), date(date,date,date,date). This is my four columns with four rows. itemNumber can be only 1 or 2. ID1 is primary key. I want only those ID's having itemNumber=2 and if count(itemNumber) grouped by ID is greater than 1, then my time constraint needs be changed as said in previous comments – vamsikrishna mannam Nov 07 '17 at 13:30
  • So from that data what is your desired output? – kjmerf Nov 07 '17 at 13:37
  • i only need“Id’s” where “itemNumber” is 2, and then if count(ItemNumber) is 1 when grouped by "ID1" which is not primary key it should filter whole rows to date between today and today+1, otherwise if that count is 2, then filter by date between today and today+2. – vamsikrishna mannam Nov 07 '17 at 13:41
  • What I am asking for is an example of what your data looks like, and what the output of your query should be. So based on your previous comment, what you would actually see in the output? – kjmerf Nov 07 '17 at 13:55
  • Lets say my date column has (11/7,11/7,11/7,11/8) and input parameter for query is 11/7, i would like to see ID(2,4) though ID-4 is in 11/8 as it satisfied condition count(itemNumber)>1 when grouped by "ID1" – vamsikrishna mannam Nov 07 '17 at 14:02
  • If that count condition fails, i want to see only ID(2) since it is only row with that date and itemNmber=2 – vamsikrishna mannam Nov 07 '17 at 14:03