Sql

Question

SELECT Sales_Master.Customer_Name ISNULL(Sales_Master.[Address]'') +''+ ISNULL(Sales_Master.[Address1]'')+''+ ISNULL(Sales_Master.[Address2]'') as 'Customer_Address' Sales_Master.Customer_ID (SUM(Sales_Discount_Point_Details.Points_Earned)-Sum(Sales_Discount_Point_Details.Points_Deducted)) As Points [OB_Debit] [OB_Credit] FROM Sales_Master INNER JOIN Sales_Discount_Point_Details ON Sales_Master.Serial_No = Sales_Discount_Point_Details.Sales_Master_Id INNER JOIN Customer ON Customer.Customer_ID =Sales_Master.Customer_ID Group by Sales_Master.Customer_Name Sales_Master.[Address] Sales_Master.[Address1] Sales_Master.[Address2] Sales_Master.Customer_ID [OB_Debit] [OB_Credit] End how can i group by only customer Id

Joginder Banger · Answer

try like this type of code. ;with customerlist(csidcompanyname) as ( select distinct CustomerID CompanyName from tbl_Customer --inner join tbl_LoginHistory lh on c.CustomerID=lh.Fk_customerID ) monthtotallogin(csidmonthlycount) as ( select fk_customerid as csidcount(*) monthlycount from tbl_LoginHistory where MONTH(login_time)= @month and year(login_time)=@years group by Fk_customerID ) totalcount(csidtotallogincount) as ( select fk_customerid as csidcount(*) as totallogincount from tbl_LoginHistory group by Fk_customerID ) Totalorderofmonth(csidmonthlyordercount) as ( select CustomerID as csidCOUNT(*) as monthlyordercount from tbl_OrderHeader where MONTH(OrderDate)= @month and year(OrderDate)=@years and TransactionStatus='Successful' group by CustomerID ) Totalordero(csidordercount) as ( select CustomerID as csidCOUNT(*) as monthlyordercount from tbl_OrderHeader group by CustomerID having CustomerID>0 ) select CS.companyname as [Company Name]isnull(ML.monthlycount0) as [This Month Total Login]isnull(TL.totallogincount0) as [Total Login Till Date]isnull(TM.monthlyordercount0) as [Total Order this Month] isnull(TOR.ordercount0)as [Total Order Till Date] from customerlist cs left join monthtotallogin ML on cs.csid=ML.csid left join totalcount TL on cs.csid=TL.csid left join Totalorderofmonth TM on cs.csid=TM.csid left join Totalordero TOR on cs.csid=TOR.csid end

Navaz · Answer

sales_master .serial_no and Sales_Discount_Point_Details.sales_master Id are reference keys sales_master .customer_Id =customer.customer_id

Joginder Banger · Answer

Hi Devoloper I bit of understand your question you have three tables Sales_Master Sales_Discount_Point_Details Customer group by not working working different column like that a addresscustomer name and so on. you need a CTE. share your tables structure