how to write group by query in sql server

[LPriyanka]

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

[v-jianboli-msft]

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.