Help with query - SQL Server Forums

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 2005 Forums

Analysis Server and Reporting Services (2005)

Help with query

Author

Topic

tyekhan
Starting Member

29 Posts

Posted - 2013-03-19 : 20:26:46

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

5072 Posts

Posted - 2013-03-19 : 23:13:05

[code]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[/code]

tyekhan
Starting Member

29 Posts

Posted - 2013-03-20 : 09:02:58

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,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 11:17:43

is TotalSales a calculated column?

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

tyekhan
Starting Member

29 Posts

Posted - 2013-03-20 : 11:59:14

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 13:06:16

can you post error message you got?

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

tyekhan
Starting Member

29 Posts

Posted - 2013-03-20 : 13:36:29

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 13:44:46

the error is obvious. you dont have the TotalSales column in any of the included tables.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources