I agree the question may be unclear, and they might just be after all faculty that have taught any CSC major. However, just in case you still need all the faculty that have taught all CSC major, this should work: The following query tells us the pairs of faculty and...

Your query is checking for sum of freight values being greater than equal to all values, so only the maximum value from year 1997 is matched , so only one row is returned if you want maximum per year, i would go with @Gordon answer, but as per comment, you...

It is either not possible, or, not with in my SQL skills to execute that in one query. I always end up using a temporary template and two SQL commands for these problems: (assuming that your table is called Table1 and the temporary table should be called tempTable) SELECT Table1.id,...

Sum is an aggregated column, invalid for use in your selection (WHERE clause). One solution is to wrap this on another SELECT. OR: SELECT A.JOBNUMBR as [Service Call], A.PONUMBER as [PO Number], SUM(A.QTYUNCMTBASE*A.UNITCOST) as [Committed Cost PO], SUM(B.WS_Committed_Cost) as [Committed Cost WS], SUM(A.QTYUNCMTBASE*A.UNITCOST) - SUM(B.WS_Committed_Cost) as [Variance] FROM POP10110 A...

MySQL extends the use of the HAVING clause to recognize column aliases defined in the SELECT. You cannot assume this behavior in other databases (it doesn't work in SQL Server and Oracle, for instance, it does work in Postgres). You have three choices: repeat the expression, use a subquery, or...

You'll need to join the table with itself to get the game list. If (as it looks) the thing that connects the game to the console is that the game's platform is set to the productname of the console, this should do it; SELECT p1.productid, p1.productname, p1.manufacturer, COUNT(p2.productid) games FROM...

To fetch all distinct taskIds which are unique from taskitem table: select ti.task_id from taskitem ti group by ti.task_id having count(1) = 1 To fetch all unique task then use this query: select t.* from task t inner join (select ti.task_id from taskitem ti group by ti.task_id having count(1) =...

One option would be to use SUM(1) in place of COUNT in a subquery, and using WHERE instead of HAVING: SELECT b.name FROM (SELECT rID,SUM(1) Sum1 FROM rating GROUP BY rID )a JOIN reviewer b ON a.rID = b.rID WHERE Sum1 >= 3 Demo: SQL Fiddle Update: Some explanation of...

Following query will return one row per ID : SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) rn FROM RESERVATIONLIST WHERE [MTH] IN ('JANUARY 2015','FEBRUARY 2015') ) T WHERE rn = 1 Note : this will return a random row from multiple rows having...

WHERE filters records before they are grouped; whereas HAVING filters the results after they have been grouped. Expressions, using functions or operators, can be used in either clause (although aggregate functions like AVG() cannot be used in the WHERE clause as the records would not have been grouped when that...

you can use case statement to return the correct label, SELECT ColumnaA, case when count(*) > 5 then '>5' else cast(count(*) as varchar(4)) end as visits from tableA group by ColumnA order by ColumnA ...

The having by clause needs to be at the end So $sql = "SELECT … column names … (calculation for distance)… as distance FROM table WHERE col1 = 'value1' AND col2 IN ('value2' , 'a different value') HAVING distance < 1" ...

It's not correct that you can't use functions in the WHERE clause. You can put almost anything in it (except aggregates) as long as it evaluates to a logical true/false condition based on the columns of the involved tables, or just constant expressions. HAVING on the other hand does support...

You can fix this by using an aggregation function on it: SELECT "events".* FROM "events" INNER JOIN "schedules" ON "schedules"."id" = "events"."schedule_id" INNER JOIN "attendees" ON "attendees"."event_id" = "events"."id" GROUP BY events.id HAVING COUNT(attendees.id) < MIN(schedules.min_attendee) Or, include it in the group by: SELECT "events".* FROM "events" INNER JOIN "schedules"...

I got it -- thanks for helping me get there, but it's pretty different than anything above. I had to take the HAVING clause out completely in order have the first part of the SELECT statement (WillCanvass) be completely independent from the actiontable part of it: SELECT a.team as team,...

select columnA from tableA where columnB in (1,2) group by columnA having count(distinct columnB) = 2 Having both values 1 and 2 in columnB fullfills automatically count(columnA) > 1 If you want either the columnB condition (columnB having 2 values) or the columnA condition then do select columnA from tableA...

As Alexandre in his comment said, your design is more than problematic. Your example is faulty too: T The categories 8 - 12 should have the gid 2 not 1. The query should return "Henry Ford" too, because he lives in the USA and works on Cars. Following not beautiful...

It is a common trap: as soon as you add a WHERE- about a LEFT JOINed table, you logically and implicitely make it an inner join as long as you do not add a hint about NULL. SELECT * FROM tcredit LEFT JOIN tcredit_order ON tcredit.id_credit=tcredit_order.id_credit GROUP BY tcredit.id_credit HAVING...

SELECT a.customer_no, SUM(a.sli_paid_amt), MAX(a.pmt_rcvd_amt) AS [Payment Amount], MAX(some_other_field), ... from LV_CHC_TOURS_RSV_DATA a left outer join LT_CHC_TOURS_RSV_LANG b on a.language = b.id left outer join LV_CHC_TOURS_RSV_CS c on a.add_text = c.source_no Where a.create_dt between @start_dt and @end_dt group by a.customer_no having sum(a.sli_paid_amt) <> [Payment Amount] If the field is not in...

Is it fair to say that a WHERE clause will be applied on the row data before any joins are applied Nope, optimizer may change the order of joined tables. So the joined table may technically go before the table you've specified in FROM. So technically a condition from...

Those queries are functionally identical, all that's happened is that LINQ has used a subquery to supply the inner layer of the query. Consider that in a normal group query, you need HAVING to act as a second WHERE clause after the grouping, but in the subquery version, the WHERE...

....... WHERE year(CDATETIME) IN ('2008','2010','2014') GROUP BY BOXNUMBER ; The above query gives more rows because you are not applying any condition on Group by clause and the other query ...... GROUP BY BOXNUMBER HAVING year(CDATETIME) IN ('2008','2010','2014'); here you are applying the condition on group by that what...

Perhaps this is what you're looking for using iif in your order by clause: SELECT listOfAllFaculty.[ID], listOfAllFaculty.[lastName], ... FROM listOfAllFaculty WHERE (((listOfAllFaculty.department1)=[Type Department1])) Or (((listOfAllFaculty.department2)=[Type Department2])) Or (((listOfAllFaculty.affiliation1)=[Type Affiliation1])) Or (((listOfAllFaculty.affiliation2)=[Type Affiliation2])) Or (((listOfAllFaculty.affiliation3)=[Type Affiliation3])) Or (((listOfAllFaculty.affiliation4)=[Type Affiliation4])) Or...

You are using the having clause incorrectly. The following is closer to what you want: select tm.user_id, tm.user_message, tm.date_message from tchat t inner join tchat_message tm on t.id = tm.user_id inner join (select user_id, max(date_message) as maxdm from tchat_message group by user_id ) tmm on tmm.user_id = tm.user_id and tmm.maxdm...

This is a very common mistake among people learning SQL. In most databases, your query would fail, because you cannot take the max() of a count(). I'm surprised that it works in MySQL, but indeed it does (here is the SQL Fiddle). In any case, you need a subquery for...

I don't fully comprehend that data scenario your dealing with, when you say you want to return one row, from what table do you want the row from, and what table is causing the multiple records? selecting a simple row from the db is not possible when you are grouping....

Eliminate the groups which has another user than 1 or 2 You are on the right track, but you don't need GROUP BY - you can use DISTINCT instead. To get the results you're looking for you can use NOT IN instead of HAVING: SELECT DISTINCT MovieID FROM Watched...

You may extend your WHERE clause to include other criteria using AND. And if you are going to SUM, you should use GROUP BY. SELECT SUM(prix) from notes_de_frais WHERE `date` BETWEEN '2015-05-01' AND '2015-06-06' AND `name`='Mr. X' AND `type`='plane tickets' GROUP BY `name`; ...

Use HAVING clause instead of SUM in WHERE condition Try this: SELECT u.user_id, u.name, i.currency, SUM(i.amount) invoiceAmount FROM mydb.users u INNER JOIN mydb.invoices i ON u.user_id = i.user_id WHERE i.is_paid = 0 GROUP BY u.user_id, i.currency HAVING SUM(i.amount) >= (CASE i.currency WHEN 'usd' THEN 100 WHEN 'gbp' THEN 155 WHEN...

Answer by comment from Inanda Menezes @Inanda: Look at it: sqlfiddle.com/#!2/5294e4/6 , I just removed the backticks from the alias used inside the having and your example worked. According to mysql documentation you don't need to use backticks on identifiers that does not have special words or characters. If you...

Why are you using both a where and a having clause with no aggregation? Your query is equivalent to: SELECT user, points FROM table1 WHERE (user <= 'paul3' and points = 1300) OR (points > 1300) ORDER BY points DESC, user ASC If you want to count the rows, then...

If I understand correctly, you want travel ids where the id of the first city is less than the id of the second. If so, then this may be what you are looking for: select c1.travelid from cities c1 join cities c2 on c1.city = $CITY1 and c2.city = $CITY2...

Just do a: SELECT COUNT(USER2) FROM tablename GROUP BY USER2 And see if it returns 1 record, or the first record value is equal to the total record count. Here is an example: $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " ....

Please give this query a shoot SELECT COUNT(DISTINCT(u.id)) AS users_count FROM users AS u INNER JOIN ( SELECT user_id, COUNT(DISTINCT profile_option_id) AS total FROM profile_answers WHERE profile_option_id IN (37,86,102) GROUP BY users.id HAVING COUNT(DISTINCT profile_option_id) = 3 ) AS a ON a.user_id = u.id If you have lots of data...

Your syntax would not work in most databases. It would happen to work in MySQL, because MySQL allows having clauses for non-aggregation queries. Even in MySQL, the clause would do nothing, because "15" is simply a number. In a boolean context, non-zero numbers are interpreted as "true" and zeros as...