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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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..?
 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
8781 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  
 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.08 seconds. Powered By: Snitz Forums 2000