Efficiently Include Column Not in Group by of SQL Query

Efficiently Include Column not in Group By of SQL Query

You can try something like this:

   ;WITH GroupedData AS
   (
       SELECT FkId, COUNT(FkId) As FkCount
       FROM B 
       GROUP BY FkId
   ) 
   SELECT gd.*, a.Name
   FROM GroupedData gd
   INNER JOIN dbo.A ON gd.FkId = A.FkId

Create a CTE (Common Table Expression) to handle the grouping/counting on your Table B, and then join that result (one row per FkId) to Table A and grab some more columns from Table A into your final result set.

Keep Column which is not in aggregate function in group by statement

I would suggest this straight forward way and translate your description "outputs the row with the MAX(amount) per customer and all of its values" to SQL:

select * from a_table t
where (t.customerid,t.amount) in (
   select customerid,max(amount) from a_table group by customerid);

Let me know if you need more input.

In a grouped SQL query, efficiently discard groups that have or do not have some values in a column

You can try with EXISTS and NOT EXISTS

SELECT person_id, Group_concat(position_id SEPARATOR ', ') AS positions
from test t
WHERE EXISTS  ( SELECT person_id 
                     FROM test t1 
                     WHERE t.person_id=t1.person_id
                     AND t1.position_id=30 
                   ) 
AND  NOT EXISTS  (  SELECT person_id 
                     FROM test t2 
                     WHERE t.person_id=t2.person_id
                     AND t2.position_id=28  )
GROUP BY person_id ;

Result:

person_id positions
    1     30, 99, 98

Demo

Aggregate functions - getting columns not included in the GROUP BY clause

Here's one way, using an inner join to filter rows with the lowest price:

select i.* 
from items i
inner join (
    select item_id, min(price) as minprice
    from items 
    group by item_id
) cheapest on i.price = cheapest.minprice
    and i.item_id = cheapest.item_id

If there are multiple sources with the lowest price, they will all show up.

EDIT: Since your question mentions the items view takes long to build up, you could store the result in a temporary table.

create temporary table temp_items 
as 
select item_id, source, price from YourView

And then run the grouping query on the temporary table. In MySQL, a temporary table is visible only to the current connection, and is dropped automatically when the connection is closed.

How to efficiently perform union of two queries with and without group by

You can add the WITH ROLLUP clause to your GROUP BY and it will add an aggregate row to the end of your output i.e.

SELECT SUM(col1), SUM(col2)...
FROM table
GROUP BY class WITH ROLLUP

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

---

Related Topics