SQL WHERE Temporary Column

Question

I have this MSSQL Query which works

SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS
 CategoryName, count(ic.CategoryId) AS NumPhotos,
  c.Slug, c.ParentCategoryId, c.Id
FROM Categories
 c LEFT JOIN
 ItemCategories ic
 on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
ORDER BY ParentCategoryId DESC

And I want to return only rows, WHERE NumPhotos>0 but temporary columns are not allowed on SQL WHERE clause

`HAVING count(ic.CategoryId) > 0` -- NumPhotos is just an aliased name for that count. — devlin carnate, Dec 01 '15 at 20:54
Possible duplicate of [SQL query for finding records where count > 1](http://stackoverflow.com/questions/7151401/sql-query-for-finding-records-where-count-1) — Tab Alleman, Dec 01 '15 at 21:17
If I add HAVING count... then I get this error Msg 156, Level 15, State 1, Line 8 Incorrect syntax near the keyword 'GROUP'. — Mario, Dec 01 '15 at 22:20
If I add it after ORDER BY then it work, please add it as a solution — Mario, Dec 01 '15 at 22:22

score 2 · Accepted Answer · answered Dec 01 '15 at 22:40

The having clause is the canonical solution to your problem. However, I suspect that an inner join would also be appropriate:

SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS CategoryName,
       count(ic.CategoryId) AS NumPhotos,
       c.Slug, c.ParentCategoryId, c.Id
FROM Categories c INNER JOIN
     ItemCategories ic
     on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
ORDER BY ParentCategoryId DESC;

Without sample data, it is hard to be sure, but I'm pretty sure this does the same thing.

The advantage of an inner join is that it might be more efficient, because less (maybe only slightly less) data would be processed and the optimizer has more opportunities to pick the best join algorithm.

score 1 · Answer 2 · answered Dec 01 '15 at 22:16

1

Existing comments have given adequate answers, but here is another solution, using a "virtual table":

SELECT * FROM ( 
SELECT c.CategoryName + ' (' + cast(count(ic.CategoryId) as varchar(255)) + ')' AS
 CategoryName, count(ic.CategoryId) AS NumPhotos,
  c.Slug, c.ParentCategoryId, c.Id
FROM Categories
 c LEFT JOIN
 ItemCategories ic
 on ic.CategoryId = c.Id
GROUP BY c.CategoryName, c.slug, c.ParentCategoryId, c.id
) 
WHERE NumPhotos>0
ORDER BY ParentCategoryId DESC

answered Dec 01 '15 at 22:16

Turophile

3,367
1
13
21

It seems that it also works with HAVING count... after the ORDER BY, and it's much faster. – Mario Dec 01 '15 at 22:22
running your query gives me this error: Msg 1033, Level 15, State 1, Line 11 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. – Mario Dec 01 '15 at 22:29

SQL WHERE Temporary Column

2 Answers2