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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors