asked
Grouping by multiple columns
Hello, I have the following table: ID bigint, Name varchar(50), Address varchar(250), RecDate smalldatetime, PurPrice decimal(7,2), PaidBy varchar(50) I am trying to come up with a select statement that will group the results by ID and sums the PurPrice but also at the same time group the returned result by month extracted from RecDate. Is this possible in one statement? example of output would be something like this: ID Name SumPrice Month 1 Test 1500 01 1 Test 1000 02 etc... Thanks in advance
Can you increase your sample size and also include expected results so we understand clearly what you want and the expected output Otherwise the below will do if I understand your requirements correctly: DROP TABLE IF EXISTS dbo.sale CREATE TABLE Sale ( SaleID INT , Name VARCHAR(100) , Price INT , RecDate DATETIME ); /* test data*/ INSERT INTO dbo.Sale ( SaleID, Name, Price, RecDate ) VALUES ( 1, 'Test1', 1500, GETDATE() ), ( 1 , 'Test1', 3000, '20161012' ), ( 2, 'Test2', 2000, '20160913' ), ( 2, 'Test2', 860, '20160930' ); /*Query */ SELECT SaleID , [Name] , SUM(Price) AS TotalAmount , DATEPART(mm, RecDate) AS MonthNo FROM dbo.Sale GROUP BY DATEPART(mm, RecDate) , SaleID , [Name] ORDER BY SaleID
18 People are following this question.
