SQL Select Where or Having

Question

Im attempting to get some records from a table based on certain factors.

One of the factors is simply with fields on the same table, the other is when joining to another table, I want to compare the number of records in the joined table to a field on the first table. Below is a sample code.

select * from tDestinations D 
left join tLiveCalls LC on LC.DestinationID = D.ID 
where D.ConfigurationID = 1486 
AND (D.Active = 1 AND D.AlternateFail > GETDATE())
-- Having COUNT(LC.ID) = D.Lines

Now from the code above I cant have the Count function in the where clause, and I cant have a field in in the having clause without it being in a function.

Im probably missing something very simple here. But I cant figure it out.

Any help is appreciated it.

EDIT: I do apologise should have explained the structure of the tables, the Destinations are single records, which the LiveCalls table can hold multiple records based on the Destinations ID (foreign key).

Thank you very much for everyones help. My final code:

select D.ID, D.Description, D.Lines, D.Active, D.AlternateFail, D.ConfigurationID, COUNT(LC.ID) AS LiveCalls from tDestinations D 
left join tLiveCalls LC on LC.DestinationID = D.ID 
where D.ConfigurationID = @ConfigurationID
AND (D.Active = 1 AND D.AlternateFail > GETDATE())
GROUP BY D.ID, D.Description, D.Lines, D.Active, D.AlternateFail, D.ConfigurationID
HAVING COUNT(LC.ID) <= D.Lines

`WHERE` deals with rows, `HAVING` deals with groups, see this: http://stackoverflow.com/a/1131076/65223 Aggregate functions work on groups. As a result, they need to be in the `HAVING` and not the `WHERE` — KM., Apr 11 '12 at 15:38

Cristian Lupascu · Accepted Answer · 2012-04-11T15:12:11.210

2

The simple thing you're missing is the GROUP BY statement.

As JNK mentioned in the comments below, you cannot use an aggregate function (such as COUNT, AVG, SUM, MIN) if you don't have a GROUP BY clause, unless your SELECT statement only references literal values (and no column names).

Your code should probably be something like:

SELECT <someFields>
FROM tDestinations D 
LEFT JOIN tLiveCalls LC on LC.DestinationID = D.ID 
WHERE D.ConfigurationID = 1486 
AND (D.Active = 1 AND D.AlternateFail > GETDATE())
GROUP BY <someFields>
HAVING COUNT(LC.ID) = D.Lines

Note that you have to specify the selected fields explicitely, in both the SELECT and GROUP BY statements (no * allowed).

edited Apr 11 '12 at 15:12

answered Apr 11 '12 at 14:43

Cristian Lupascu

39,078
16
100
137

See [docs](http://msdn.microsoft.com/en-us/library/ms180199(v=sql.90).aspx): `group by` is not necessary. – Matt Fenwick Apr 11 '12 at 14:43
2

this would be upvote-able if you explained it a little further for the OP – JNK Apr 11 '12 at 14:43
@MattFenwick - It is if you want to use an aggregate function! – JNK Apr 11 '12 at 14:44
Thats brilliant, thank you very much. Im assuming the Select has to match the Group by . – Valeklosse Apr 11 '12 at 14:51
@Valeklosse yes, they have to match; they are in fact the grouping criteria – Cristian Lupascu Apr 11 '12 at 14:52
"you cannot use an aggregate function [in the `HAVING` clause] if you don't have a `GROUP BY` clause" -- that's only true when you use column names in the `SELECT` clause; if the `SELECT` clause contains only literals then a `GROUP BY` may be omitted e.g. `SELECT 1 FROM YourTable HAVING MIN(c) = MAX(c);` – onedaywhen Apr 11 '12 at 15:07
@onedaywhen You are right, I have modified my answer according to your suggestion – Cristian Lupascu Apr 11 '12 at 15:12
@onedaywhen I actually haven't been aware of that; thank you for sharing – Cristian Lupascu Apr 11 '12 at 15:13

score 1 · Answer 2 · answered Apr 11 '12 at 14:49

you can only use having with aggregations. Actually having is the "where clause" for aggregation, BUT you can still have a where on the columns that you are no aggregating.

For example:

SELECT TABLE_TYPE, COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
where TABLE_TYPE='VIEW'
group by TABLE_TYPE
having COUNT(*)>1

In your case you need to use havving count(*)=1 so, I think your query would be something like this:

select YOUR_COLUMN 
from tDestinations D 
left join tLiveCalls LC on LC.DestinationID = D.ID 
where D.ConfigurationID = 1486 AND (D.Active = 1 AND D.AlternateFail > GETDATE())
group by YOUR_COLUMN
Having COUNT(LC.ID) = value

SQL Select Where or Having

2 Answers2