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
 General SQL Server Forums
 New to SQL Server Programming
 Debug SQL syntax

Author  Topic 

stoolpidgeon
Starting Member

28 Posts

Posted - 2013-06-14 : 10:03:45
I'm receiving the following error from the preceeding code:

Select Supplier,
SUM(Select InvoiceValue From SalesHistory Where ImportDate = '01/01/2013') As January,
SUM(Select InvoiceValue from SalesHistory Where ImportDate = '02/01/2013') As February
From SalesHistory
Group By Supplier

Error:

Incorrect syntax near the keyboard 'Select'
Incorrect syntax near ')'
Incorrect syntax near the keyboard 'From'

ps. I'm aware that the crosstab wont be summing over supplier yet, that stage I plan to implement next

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-14 : 10:19:21
Sum cannot be used over a select statment (the way you used it)

You may rewrite the query as below

SELECT
Supplier
,Sum(Case When ImportDate='2013-01-01' Then InvoiceValue END) as Jan
,Sum(Case When ImportDate='2013-02-01' Then InvoiceValue END) as Feb
FROM SalesHistory
Group by Supplier

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-14 : 10:43:18
[code]
Select Supplier,
[January],
[February]
From (SELECT Supplier,DATENAME(mm,ImportDate) AS DateMonth,InvoiceValue FROM SalesHistory)t
Pivot(SUM(InvoiceValue) FOR DateMonth IN ([January],[February]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -