Summarizing Sum by Sales Type in One Line..? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Summarizing Sum by Sales Type in One Line..?

Author

Topic

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-05 : 04:05:17

Good day!

I want to generate the Sum of Sales base on a given date by Salestpye but it seems that the query does not recognize the column (totalsales,salestype) even if its correct. In short the result must be:


cash      credit       pay      Bad Order
1,000.00  4,000.00   3,000.00   6,000.00

Here is my code:


CREATE PROCEDURE [dbo].[sp_Get_Sumof_Sales]
	-- Add the parameters for the stored procedure here
	@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin

	SET NOCOUNT ON;
	
	Select transdate,totalsales,salestype FROM tbl_pos_sales_summary
	PIVOT (SUM(totalsales) FOR salestype IN ([cash], [credit], [pay], [Bad Order])) as SumofSales
	
	WHERE (CONVERT(Date, transdate)) BETWEEN  @fromdate AND @todate
	GROUP BY transdate,totalsales,salestype
	
END

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-05 : 04:51:44

Ive modified the query to this to make it simple without pivot: How can i make the the salestype in one line?


ALTER PROCEDURE [dbo].[sp_Get_Sumof_Sales]
	-- Add the parameters for the stored procedure here
	@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin

	SET NOCOUNT ON;
	
	 SELECT transdate,SUM(totalsales) AS Total,salestype FROM tbl_pos_sales_summary
	 WHERE (CONVERT(Date, transdate)) BETWEEN  @fromdate AND @todate
	 GROUP BY transdate,salestype
END

Now I got this result:


transdate    Total       Salestype
2012-10-01   10,000.00   Bad Order
2012-11-02   5,000.00    Bad Order 
2012-11-03   13,000.00   cash 
2012-11-04   12,000.00   cash
2012-11-04   4,000.00    credit 
2012-11-04   5,000.00    credit

So the result must be:


   Total       Salestype
   15,000.00   Bad Order
   25,000.00   cash 
   9,000.00    credit

And I dont want to display the date column.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-05 : 05:24:44

quote:
Originally posted by adbasanta

Ive modified the query to this to make it simple without pivot: How can i make the the salestype in one line?

ALTER PROCEDURE [dbo].[sp_Get_Sumof_Sales]
-- Add the parameters for the stored procedure here
@fromdate AS varchar(50), @todate AS varchar(50)
AS
Begin

SET NOCOUNT ON;

SELECT ~~transdate,~~ SUM(totalsales) AS Total,salestype FROM tbl_pos_sales_summary
WHERE (CONVERT(Date, transdate)) BETWEEN @fromdate AND @todate
GROUP BY ~~transdate,~~ salestype
END

Now I got this result:

transdate Total Salestype
2012-10-01 10,000.00 Bad Order
2012-11-02 5,000.00 Bad Order
2012-11-03 13,000.00 cash
2012-11-04 12,000.00 cash
2012-11-04 4,000.00 credit
2012-11-04 5,000.00 credit

So the result must be:

Total Salestype
15,000.00 Bad Order
25,000.00 cash
9,000.00 credit

And I dont want to display the date column.

Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Too old to Rock'n'Roll too young to die.

adbasanta
Posting Yak Master

120 Posts

Posted - 2012-11-05 : 05:26:46

OMG thanks webfred!

I did not notice that simple thing!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources