Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i am having two tables table 1 and table 2 with columns:
table 1 table 2
sales id sales id
customer id sales amount
product id frequency(as daily or monthly eg; frequency=1 'daily' else 'monthly' )
order number
person
i had wrote a query like
select t1.sales id, t1.customer id,COALESCE(sales amount, null ) as amount
from table 1
left join table 2 (select sales id, max(sales amount) as sales amount
from table 2
group by sales id)
t2 on t2.sales id = t1.sales id
but here i want to include frequency column also and it should be group by customer id
can anyone please help me to solve this in sql query
Hi @LPriyanka ,
You can modify your query to include the frequency column by adding it to the select statement and the group by clause.
Here's an example query:
SELECT
t1.sales_id,
t1.customer_id,
COALESCE(t2.sales_amount, null) AS amount,
t2.frequency
FROM
table1 t1
LEFT JOIN (
SELECT
sales_id,
MAX(sales_amount) AS sales_amount,
frequency
FROM
table2
GROUP BY
sales_id,
frequency
) t2 ON t2.sales_id = t1.sales_id
GROUP BY
t1.customer_id,
t1.sales_id,
t2.frequency
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
12 | |
2 | |
2 | |
1 | |
1 |