Select multiple columns from a table, but group by one

Solution 1:

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.

Solution 2:

I just wanted to add a more effective and generic way to solve this kind of problems. The main idea is about working with sub queries.

do your group by and join the same table on the ID of the table.

your case is more specific since your productId is not unique so there is 2 ways to solve this.

I will begin by the more specific solution: Since your productId is not unique we will need an extra step which is to select DISCTINCT product ids after grouping and doing the sub query like following:

WITH CTE_TEST AS (SELECT productId, SUM(OrderQuantity) Total
                    FROM OrderDetails
                    GROUP BY productId)
SELECT DISTINCT(OrderDetails.ProductID), OrderDetails.ProductName, CTE_TEST.Total
FROM OrderDetails 
INNER JOIN CTE_TEST ON CTE_TEST.ProductID = OrderDetails.ProductID

this returns exactly what is expected

 ProductID     ProductName         Total
    1001          abc               12    
    1002          abc               23
    2002          xyz               8
    3004          ytp               15
    4001          aze               19

But there a cleaner way to do this. I guess that ProductId is a foreign key to products table and i guess that there should be and OrderId primary key (unique) in this table.

in this case there are few steps to do to include extra columns while grouping on only one. It will be the same solution as following

Let's take this t_Value table for example:

If i want to group by description and also display all columns.

All i have to do is:

1. create WITH CTE_Name subquery with your GroupBy column and COUNT condition
2. select all(or whatever you want to display) from value table and the total from the CTE
3. INNER JOIN with CTE on the ID(primary key or unique constraint) column

and that's it!

Here is the query

WITH CTE_TEST AS (SELECT Description, MAX(Id) specID, COUNT(Description) quantity 
                    FROM sch_dta.t_value
                    GROUP BY Description)
SELECT sch_dta.t_Value.*, CTE_TEST.quantity 
FROM sch_dta.t_Value 
INNER JOIN CTE_TEST ON CTE_TEST.specID = sch_dta.t_Value.Id

And here is the result: