Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

QUERY HELP Expand / Collapse
Author
Message
Posted Thursday, December 20, 2012 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668
Hi geniuses!

I have this query

SELECT DISTINCT chapter,  Sum(dbo.FGG.Value) AS FGG2010
FROM FGG
WHERE (FGG.YEAR= 2010) and (dbo.FGG.valueType = '02 - FGG01l')
Group by chapter

It retrieves :

chapter | FGG2010
A | 1$
B | 2$
C | 3$


What I want is to add in 'Sum(dbo.FGG.Value) AS FGG2011' where FGG.YEAR=2011 in the same query, in order to retrive:

chapter | FGG2010 | FGG2011
A | 1$ | 2$
B | 2$ | 33$
C | 3$ | 11$


Thanks in advance!
Regards
Post #1398947
Posted Thursday, December 20, 2012 6:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:45 AM
Points: 5,237, Visits: 9,487
Use a CASE expression in your SUM statement, something like this:

...
SUM(CASE WHEN Year = 2010 THEN Amount ELSE 0 END) AS Total2010,
SUM(CASE WHEN Year = 2011 THEN Amount ELSE 0 END) AS Total2011
...


Alternatively, you could use PIVOT. Or you could write a report in Reporting Services to display your data in the way you need.

John
Post #1398951
Posted Thursday, December 20, 2012 7:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:56 AM
Points: 178, Visits: 668

THANKS MEN!
Post #1398953
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse