Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LPriyanka
New Member

how to write group by query in sql server

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

 

1 REPLY 1
v-jianboli-msft
Community Support
Community Support

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.