SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Summarizing Sum by Sales Type in One Line..?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

120 Posts

Posted - 11/05/2012 :  04:05:17  Show Profile  Reply with Quote
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

Edited by - adbasanta on 11/05/2012 04:59:42

adbasanta
Posting Yak Master

120 Posts

Posted - 11/05/2012 :  04:51:44  Show Profile  Reply with Quote
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

Edited by - adbasanta on 11/05/2012 04:54:08
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8679 Posts

Posted - 11/05/2012 :  05:24:44  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 11/05/2012 :  05:26:46  Show Profile  Reply with Quote
OMG thanks webfred!

I did not notice that simple thing!


-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000