I need some help in writing up this SQL query using a single table. Something like this
| User ID | Category | Spend | Transactions | Country |
|---|---|---|---|---|
| 1 | Sport | 30 | 2 | USA |
| 1 | Bills | 60 | 3 | USA |
| 2 | Sport | 10 | 1 | MEX |
| 3 | Grocery | 50 | 8 | CAN |
| 2 | Grocery | 70 | 4 | MEX |
| 3 | Sport | 20 | 5 | CAN |
| 3 | Bills | 30 | 2 | CAN |
| 1 | Petrol | 60 | 5 | USA |
I then want to group the rows by the User id and group the spend and transactions each by the category and having the country as a column by itself like this.
| User ID | Sport_Spend | Bills_Spend | Grocery_Spend | Petrol_Spend | Sport_Transactions | Bills_Transactions | Grocery_Transactions | Petrol_Transactions | Country |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 30 | 60 | 0 | 60 | 2 | 3 | 0 | 5 | USA |
| 2 | 10 | 0 | 70 | 0 | 1 | 0 | 4 | 0 | MEX |
| 3 | 20 | 30 | 50 | 0 | 5 | 2 | 8 | 0 | CAN |
Its stumping me a bit would appreciate some help.
CodePudding user response：
@jarlh comments are most relevant and need to be addressed. But here is something to start with: (ms sql code) (I opted out from transactions columns to reduce the problem, but the coding is just the same) https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=25550539029ba1c4be0826725bf9e00a
with data (UserID,Category,Spend,Transactions,Country) as(
select 1,'Sport',30,2,'USA' union all
select 1,'Bills',60,3,'USA' union all
select 2,'Sport',10,1,'MEX' union all
select 3,'Grocery',50,8,'CAN' union all
select 2,'Grocery',70,4,'MEX' union all
select 3,'Sport',20,5,'CAN' union all
select 3,'Bills',30,2,'CAN' union all
select 1,'Petrol',60,5,'USA'
)
select UserID
,isnull(SUM([Sport]),0)as Sport
,isnull(SUM([Bills]),0)as Bills
,isnull(SUM([Grocery]),0)as Grocery
,isnull(SUM([Petrol]),0)as Petrol
,MAX(Country)as Country
from (
select UserID,Category,Spend,Transactions,Country
from data) p
PIVOT(
SUM(SPEND)
For CATEGORY in ([Sport] ,[Bills] ,[Grocery] ,[Petrol])
)as PivotTable
group by UserID
CodePudding user response：
select
COALESCE(user_id,0) as user_id,
COALESCE(Sport_Spend,0) as Sport_Spend,
COALESCE(Bills_Spend,0) as Bills_Spend,
COALESCE(Grocery_Spend,0) as Grocery_Spend,
COALESCE(Petrol_Spend,0) as Petrol_Spend,
COALESCE(Sport_Transactions,0) as Sport_Transactions,
COALESCE(Bills_Transactions,0) as Bills_Transactions,
COALESCE(Grocery_Transactions,0) as Grocery_Transactions,
COALESCE(Petrol_Transactions,0) as Petrol_Transactions
,country from
(SELECT DISTINCT user_id,country from table_name) as A
LEFT JOIN
(select user_id, spend as Sport_Spend ,transactions as Sport_Transactions from table_name where category='Sport') as B using (user_id)
LEFT JOIN
(select user_id, spend as Bills_Spend ,transactions as Bills_Transactions from table_name where category='Bills') as C using (user_id)
LEFT JOIN
(select user_id, spend as Grocery_Spend ,transactions as Grocery_Transactions from table_name where category='Grocery') as D using (user_id)
LEFT JOIN
(select user_id, spend as Petrol_Spend ,transactions as Petrol_Transactions from table_name where category='Petrol') as E using (user_id)
ORDER BY user_id;