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 2005 Forums
 Analysis Server and Reporting Services (2005)
 Help with query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tyekhan
Starting Member

28 Posts

Posted - 03/19/2013 :  20:26:46  Show Profile  Reply with Quote
Hi,

I have one field in the database that have 5 different types of outcome , I need a query that will do the following,

Area saletype Total
1 , A45 , 4
2 , A007 , 5
2 , A005 , 2
3 , A775 , 5
3 , A001 , 2
4 , A775 , 4
5 , 4007 , 2


what I would like to do is if the below saletype are used then to times the amount as below,
A007 *2
A005 *3
A775 *4

all the other saletype will be just a count apart from the above they will need times the figures, so the above table will look like this,

Area saletype Total
1 , A45 , 4
2 , A007 , 10
2 , A005 , 6
3 , A775 , 20
3 , A001 , 2
4 , A775 , 20
5 , 4007 , 2

I do have a query that will do 1 of the figures not all three.

SELECT Area, SUM(CASE [Saletype] WHEN 'A007' THEN Total * 2 ELSE Total END) AS Total
FROM overall
GROUP BY Area

overall finish's table would look like this

Area Total
1 , 4
2 , 16
3 , 22
4 , 20
5 , 2

russell
Pyro-ma-ni-yak

USA
5071 Posts

Posted - 03/19/2013 :  23:13:05  Show Profile  Visit russell's Homepage  Reply with Quote
SELECT	area,
	SUM (CASE saletype
		WHEN 'A007' THEN total * 2
		WHEN 'A005' THEN total * 3
		WHEN 'A775' THEN total * 4
		ELSE total
		END
	)
FROM	yourtable
GROUP BY area
Go to Top of Page

tyekhan
Starting Member

28 Posts

Posted - 03/20/2013 :  09:02:58  Show Profile  Reply with Quote
thanks, that worked fine but i'm added other table but it does not work for that,

i have the below but it does not work,

SELECT     Overall.Place, 
                      SUM(CASE saletype WHEN 'EE002' THEN TotalSales * 2 WHEN 'EE003' THEN TotalSales * 3 WHEN 'EE004' THEN TotalSales * 4 ELSE TotalSales END)
                       AS totalsales
                       
                       FROM         Area INNER JOIN
                      Overall ON Area.AreaID = Overall.AreaID INNER JOIN
                      saletypeall ON Area.Salecode = saletypeall.Salecode
WHERE     (Overall.ImportDate > CONVERT(DATETIME, '2013-02-24 00:00:00', 102))
GROUP BY Overall.Place


This is coming up with an error,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2013 :  11:17:43  Show Profile  Reply with Quote
is TotalSales a calculated column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tyekhan
Starting Member

28 Posts

Posted - 03/20/2013 :  11:59:14  Show Profile  Reply with Quote
Not in this query thats what i'll need to do but not sure how to do it

quote:
Originally posted by visakh16

is TotalSales a calculated column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2013 :  13:06:16  Show Profile  Reply with Quote
can you post error message you got?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

tyekhan
Starting Member

28 Posts

Posted - 03/20/2013 :  13:36:29  Show Profile  Reply with Quote
Its the below,

SQL Execution Error.

Exection SQL Statement: SELECT Overall.Place,
SUM(CASE saletype WHEN 'EE002' THEN TotalSales * 2 WHEN 'EE003' THEN TotalSales * 3 WHEN 'EE004' THEN TotalSales * 4 ELSE TotalSales END)
AS totalsales

FROM Area INNER JOIN
Overall ON Area.AreaID = Overall.AreaID INNER JOIN
saletypeall ON Area.Salecode = saletypeall.Salecode
WHERE (Overall.ImportDate > CONVERT(DATETIME, '2013-02-24 00:00:00', 102))
GROUP BY Overall.Place
Error Message: Invalid column name 'TotalSales'.
Error Invalid column name 'TotalSales'.

Error Invalid column name 'TotalSales'.

Error Invalid column name 'TotalSales'.

quote:
Originally posted by visakh16

can you post error message you got?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/20/2013 :  13:44:46  Show Profile  Reply with Quote
the error is obvious. you dont have the TotalSales column in any of the included tables.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.12 seconds. Powered By: Snitz Forums 2000