cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors