Using Group by and Having Clause

Group by with having clause a key not equal

If you want for each item_name the 1st non 0 (for the quantity) row then you can do it with a correlated subquery:

SELECT t.*
FROM tablename t
WHERE t.id = (SELECT MIN(id) FROM tablename WHERE item_name = t.item_name AND qty <> 0)

See the demo.

Results:

> id | item_name | qty
> -: | :-------- | --:
> 25 | Potatoes  | 500
> 28 | test      |  88
> 29 | Wheat     |   3
> 30 | abc       |  10

Group By with date column in the having clause

The HAVING clause filters results after the grouping and aggregation, so in order to do this any fields you want in the HAVING clause must be in the GROUP BY or SELECT lists. The WHERE clause filters the rows before doing the grouping and aggregation.

To get the results you want, simply move the date between... condition into a WHERE clause:

SELECT customer,SUM(price)
  FROM table
  WHERE date BETWEEN 3-Jan-2015 AND 8-Jan-2015
  GROUP BY customer 

SQL - using 'HAVING' with 'EXISTS' without using 'GROUP BY'

well the error is clear in first query UnitPrice is not part of aggregation nor group by
whereas in your second query you are comparing p.unitprice from table "products p" which doesn't need to be part of aggregation or group by , your second query is equivalent to :

select * from products p
where p.unitprice > (select avg(unitprice) FROM products)

which maybe this is more clear , that sql caculate avg(unitprice) then compares it with unitprice column from product.

SQL query with Group By and Having Clause in LINQ structure

You do not need HAVING here and Grouping should be provided by constant, because you have filter on grouping keys:

var data = context.WholesaleCredit
    .Where(x => x.ExpirationUTCDateTime > DateTime.Now && x.RegisteredCustomerId == registeredCustomerId && x.Site.Id == siteid)
    .GroupBy(x => 1)
    .Select(g => new 
    {
        TotalSiteCreditAmount = g.Sum(x => x.RemainingAmount),
        MaxExpiryDate = g.Max(x => x.ExpirationUTCDateTime)
    })
    .First();

Deleting a row from joined tables using group by and having clauses

The problem with your code is the subquery:

SELECT enrol.course_id group by enrol.course_id having count(*)<3

which, although is missing a FROM clause, it runs without a syntax error in MySql, but produces unexpected results.

Join the correct version of that subquery to the 2 tables like this:

DELETE c, e
FROM course c
LEFT JOIN enrol e ON e.course_id = c.id
LEFT JOIN (SELECT course_id, COUNT(*) count FROM enrol GROUP BY course_id) t
ON t.course_id = c.id
WHERE e.course_id IS NULL OR t.count < 3;

I use LEFT joins so that even courses with no enrolees will be deleted.

See a simplified demo.

Mysql query with group by and having clause

SELECT *
FROM user_bid 
WHERE (prodid, price) IN
(
    SELECT prodid, MAX(price)
    FROM user_bid 
    GROUP BY prod_id
)

MS Accesss - where cluase with group by and having clause

Try this:

select icode, sum(soldQty), sum(rackQty) 
from sales 
where TimeValue(dt) between #10:00:00# and #17:00:00#
group by icode 
having icode between 14000 and 16000 
order by icode

---

Related Topics