having

how to translate the SQL code "having" condition into LinqToSQL or LinqToEntites?

Could you tell me how to translate the following SQL code to Linq To SQL or Linq To Entites? The correst SQL code is: select CollectId,url,userid,pubtime from Collect group by url,collectid,userid,pubtime having pubtime >= (select max(pubtime) from collect d where d.url = collect.url ) order by Collect.pubtime desc Th...

Hibernate Criteria API - HAVING clause work arounds

I've written a query using Hibernate Criteria API to grab a summation of a particular value, now I need to be able to restrict the result to rows where that sum is greater or equal to a particular value. Normally I would use a HAVING clause in my SQL to do this, but the Criteria API doesn't seem to support that at this moment. In raw ...

How to use an aliased column in a MySQL WHERE clause?

I have a MySQL query like this: SELECT *, SUM(...some SQL removed for brevety) AS Occurrences FROM some_table AS q WHERE criterion="value" GROUP BY q.P_id ORDER BY Occurrences DESC LIMIT 10; I want to restrict the results to rows where Occurrences>0. This seems very simple to me, but I can't seem to make it work. No matter what I tr...

Complex join with nested group-by/having clause?

I ultimately need a list of "import" records that include "album" records which only have one "song" each. This is what I'm using now: select i.id, i.created_at from imports i where i.id in ( select a.import_id from albums a inner join songs s on a.id = s.album_id group by a.id having 1 = count(s.id) ); The nested sele...

is there something faster than "having count" for large tables?

Here is my query: select word_id, count(sentence_id) from sentence_word group by word_id having count(sentence_id) > 100; The table sentenceword contains 3 fields, wordid, sentenceid and a primary key id. It has 350k+ rows. This query takes a whopping 85 seconds and I'm wondering (hoping, praying?) there is a faster way to find all...

Group by 'other', where groups with small contribution are included in 'other' field - MySQL

I would like to GROUP BY an item in my table, which has an associated monetary value. This I can work out as a percentage, however there are often too many items in the list (too many to be useful, for example in a graph). Consequently, I would like to put those items that have a contribution to the total of less than say 5% into an 'Oth...

Subquery returning multiple columns

Hi, I am writing a select subquery that returns a COUNT. However, I need to use a HAVING clause in the subquery and as a result need to specify that column in my subquery SELECT. So the result is that I have 2 columns: COUNT and column_for_having_clause. Is there any way that i can tell SQL to take just the first column. I get an ...

SQL query need to get names where count(id) = 2

I have a table programparticipants, I am currently successfully querying the IDs where count(name) > 1. What I need now is to query the names that belong to those IDs where count(name) > 1. Can anyone help me please? Example, data result currently being returned: ID count(name) 1 2 3 4 4 ...

How to get away with non-grouping field in HAVING clause

When executing in *ONLY_FULL_GROUP_BY* mode, I get the error "non-grouping field 'distance' is used in HAVING clause" when executing the following query. The query counts the amount of hotels that are within 15 km distance of a certain latitude & longitude. Is there a way to rewrite this query so I don't get the error anymore in *ONLY_FU...

SQL Question: It's possibile to have a WHERE clause after a HAVING clause?

So... It is possibile to use a WHERE clause after a HAVING clause? The first thing that comes to my mind is sub queries, but I'm not sure. P.S. If the answer is affirmative, could you give some examples? ...

... where count(col) > 1 ...

I have a table like this: +-----+-----+-------+ | id | fk | value | +-----+-----+-------+ | 0 | 1 | peter | | 1 | 1 | josh | | 3 | 2 | marc | | ... | ... | ... | I'd like now to get all entries which have more than one value. The expected result would be: +-----+-------+ | fk | count | +-----+-------+ | 1 | 2 ...

'Unknown key(s): having' In Ruby on Rails ActiveRecord Find Method

I have a project which needs to use an agregrate function to sort and return relevant records from one of my active record models. Problem is, despite seeing it used on numerous rails tutorials, and despite it being in the online ActiveRecord documentation, my Rails app throws this error at me when the method is called: Unknown key(s):...

MYSQL COUNT GROUP BY question

I'd like to only select the rows where the count is greater than 1 (in other words the duplicates) right now from a few thousand records i am mostly seeing ones with a few 2s and 3s here and there SELECT count( * ) AS `Number` , GI . * FROM `GeneralInformation` AS GI GROUP BY `FirstName` , `Surname` how can I do this? ...

Linq with group by having count

Hi, how do I write this query in linq (vb.net)? select B.Name from Company B group by B.Name having COUNT(1) > 1 ...

SQL HAVING SUM GROUP BY

Using SQL Server 2005. I am building an inventory/purchasing program and I’m at the point where I need the user to “check out” equipment. When he selects a product, I need to query which stock locations have the available Qty, and tell the user which location to walk to/ retrieve product. Here is a query for a particular [StockLocation_...

Trouble with SQLite SQL Query

I'm trying to run the following query in SQLite 3: SELECT *, DISTANCE(latitude, longitude, ?, ?) AS "distance" FROM "country" WHERE "id" NOT LIKE ? HAVING "distance" <= ? ORDER BY "distance" ASC; But I get the following error: SQLSTATE[HY000]: General error: 1 a GROUP BY clause is required before HAVING I don't understand ...

SPROC T-SQL Syntax to return results if rows exist on multiple days

what I need to test for on my table is if there are rows for a given user id and order id on two separate days (DATETIME field for a timestamp). I'm pretty sure I'd need a having clause and that's why I'm here...that frightens me terribly. ...

HAVING ANY and HAVING EVERY in SQL Server 2005

Hi, Does SQL Server 2005 support ANY or EVERY with HAVING? Suppose I have two tables Training(TrainingID, TrainingCloseDate) and TrainingDetail(TrainingDetailID,TrainingID,LKClassCode,CompletionDate). For one TrainingID, there can be multiple values in TrainingDetail with different LKClassCode. I need to find all the TrainingIDs which...

SQL Server - Top Saleperson Per Region

SELECT region, person, sum(dollars) as thousands FROM sales GROUP BY region, person ORDER BY region, sum(dollars) desc The SQL above produces a complete list of sales people per region like this region person thousands canada mike smith $114 canada joe blog $76 canada pete dodd $45 usa ...

mysql php query HAVING clause

Hi! Im trying to get this query to work but i get this error: Unknown column 'zips.city' in 'having clause' `$query = "SELECT zips.* FROM zips HAVING zips.city LIKE '%$city%' AND zips.stateabbr LIKE '%$state%' LIMIT 1"; $result = mysql_query($query) or die (mysql_error());` my zips table has a...