topic how to write group by query in sql server in Developer

how to write group by query in sql server

LPriyanka — Tue, 02 May 2023 18:22:35 GMT

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

Re: how to write group by query in sql server

v-jianboli-msft — Thu, 25 May 2023 03:14:03 GMT

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.