Select Multiple Columns from a Table, But Group by One

Select all columns with GROUP BY one column

distinct on

select distinct on (key) *
from t
order by key, name

Notice that the order by clause determines which row will win the ties.

SQL: How to Select multiple columns with only one group by

Your table is not normalized. What would it mean for instance to find the same address with another latitude and longitude? That should not even be possible in a database. So you might want separate tables for addresses, phones and contact persons instead.

Anyway, with the table given, it seems you just want to see one result row per customer and don't care which address, phone and contact.

Then this would suffice:

select 
  deliverycustomer,
  min(deliveryaddress1),
  min(deliveryphone),
  min(contact_person),
  min(contact_address)
from delivery_details
group by deliverycustomer
order by deliverycustomer;

If you want the phone to belong to the address and maybe even show latitude and logitude, then you wouldn't Aggregate rows, but pick rows instead. In order to do so, you'd rank your records per customer with ROW_NUMBER.

select
  deliverycustomer,
  deliveryaddress1,
  deliveryphone,
  contact_person,
  contact_address,
  latitude,
  longitude
from
(
  select 
    dd.*, 
    row_number() over (partition by deliverycustomer order by contact_person) as rn
  from delivery_details dd
) ranked
where rn = 1
order by deliverycustomer;

How to use multiple columns but only one column in GROUP BY and ORDER BY

I think this is what you want:

SELECT 
           r.id_rekrutmen,
           r.judul_rekrutmen,
           MAX(rw.tanggal_rekrutmen) AS tanggal_rekrutmen
    FROM rekrutmen r
       INNER JOIN rekrutmen_waktu rw
          ON r.id_rekrutmen = rw.id_rekrutmen
    GROUP BY 
           r.id_rekrutmen,
           r.judul_rekrutmen,

How to select multiple columns and group by one column

It looks like you want a window sum rather than aggregation.

That is, replace:

SUM(C.amount) as FINAL_AMOUNT

With:

SUM(C.amount) OVER(PARTITION BY A.NUMBER) as FINAL_AMOUNT

Accordingly, you need to remove the GROUP BY clause from the query.

How to select multiple columns, sum one column and group by multiple columns

are you looking for this? :

select FORMAT(d.date_start, 'yyyy-MM') date_start
     , count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')

Select multiple columns with GROUP BY SQL

You can use windows function as below-

Select 
    PatientID
  , Amount
  , BillId
  , DateOfService As "Date Of Service"
from
 (SELECT  
     cd.PatientID
    ,cd.Amount
    ,cd.BillId
    ,t.DateOfService
    ,row_number() over(partition by cd.PatientID order by t.DateOfService asc) as seqnum
FROM ChargeDetail as cd inner join transactions as t 
on cd.ChargeDetailID = t.ChargeDetailID
WHERE cd.PatientID IS NOT NULL) t
Where t.seqnum = 1;

Note the MySQL & SqlServer are 2 different databases however the above approach should work for both.

---

Related Topics