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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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