Thiết kế website giá rẻ

Question

I have the following code:

select id as customerid,
tempo.team,
max(cast(t.transaction_date as date)) as latest_transaction
from custom.tdc_unique_members tum 
left join tdc."transaction" t 
    on t.transaction_patron_account_id = tum.simple_id 
left join tdc.agency a 
    on a.agency_id = t.agency_id
left join 
(
select agency.agency_code, supp.description,
 case 
    when agency.description like '%Augusta%' or agency.description like '%GreenJackets%' or agency.agency_code like '%AGJ%' then 'Augusta GreenJackets'
    when agency.description like '%Iowa%' or agency.description like '%Cubs%' then 'Iowa Cubs'
    when agency.description like '%San Jose%' or agency.description like '%Giants%' or agency.description like 'SJG%' then 'San Jose Giants'
    when agency.description like '%Memphis%' or agency.description like '%Redbirds%' then 'Memphis Redbirds'
    when agency.description like '%Midland%' or agency.description like '%RockHounds%' or agency.description like 'SJG%' then 'Midland RockHounds'
    when agency.description like '%Inland Empire%' or agency.description like '%66ers%' or agency.description like 'IE%' then 'Inland Empire 66ers'
    when agency.description like '%Winston-Salem%' or agency.description like '%Dash%' or agency.description like 'WSD%' or agency.agency_code like '%WSD%' then 'Winston-Salem Dash'
    else supp.description 
end as team
    from tdc.agency agency
left join tdc.supplier_agency supag
    on supag.agency_id = agency.agency_id 
left join tdc.supplier supp
    on supp.supplier_id = supag.supplier_id  
)tempo on tempo.agency_code = a.agency_code
where team is not null
group by customerid, tempo.team

and use the following code to check data

select id as customerid,
tempo.team,
max(cast(t.transaction_date as date)) as latest_transaction
from custom.tdc_unique_members tum 
left join tdc."transaction" t 
    on t.transaction_patron_account_id = tum.simple_id 
left join tdc.agency a 
    on a.agency_id = t.agency_id
left join 
(
select agency.agency_code, supp.description,
 case 
    when agency.description like '%Augusta%' or agency.description like '%GreenJackets%' or agency.agency_code like '%AGJ%' then 'Augusta GreenJackets'
    when agency.description like '%Iowa%' or agency.description like '%Cubs%' then 'Iowa Cubs'
    when agency.description like '%San Jose%' or agency.description like '%Giants%' or agency.description like 'SJG%' then 'San Jose Giants'
    when agency.description like '%Memphis%' or agency.description like '%Redbirds%' then 'Memphis Redbirds'
    when agency.description like '%Midland%' or agency.description like '%RockHounds%' or agency.description like 'SJG%' then 'Midland RockHounds'
    when agency.description like '%Inland Empire%' or agency.description like '%66ers%' or agency.description like 'IE%' then 'Inland Empire 66ers'
    when agency.description like '%Winston-Salem%' or agency.description like '%Dash%' or agency.description like 'WSD%' or agency.agency_code like '%WSD%' then 'Winston-Salem Dash'
    else supp.description 
end as team
    from tdc.agency agency
left join tdc.supplier_agency supag
    on supag.agency_id = agency.agency_id 
left join tdc.supplier supp
    on supp.supplier_id = supag.supplier_id  
)tempo on tempo.agency_code = a.agency_code
where team is not null
and customerid like '401820%'
group by customerid, tempo.team

but get the following ouput

I only want one customer ID and team and each customer ID is associated with a team. For example, my desired output would be

401820|WIC Wichita Wind Surge 2023-09-13
401820|POR Portland Sea Dogs 2023-08-29
401820|MEM Memphis Redbirds 2023-08-31
401820|AUG Augusta GreenJackets 2023-09-03

Why am I not getting that? I feel it is something in the grouping but I can’t figure it out.

I only want one customer ID and team and each customer ID is associated with a team. For example, my desired output would be

401820|WIC Wichita Wind Surge 2023-09-13
401820|POR Portland Sea Dogs 2023-08-29
401820|MEM Memphis Redbirds 2023-08-31
401820|AUG Augusta GreenJackets 2023-09-03

Thiết kế website giá rẻ

Danh mục

How to avoid “multiple” groups in SQL