Several queries - one table - several columns

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

Several queries - one table - several columns

Author

Topic

xhist
Starting Member

3 Posts

Posted - 2010-05-30 : 18:54:16

Hi!

I have this statement using MS Access.


SELECT Transaktioner.Typ, Sum(Transaktioner.Belopp)  AS 2009Dec
FROM Transaktioner 
WHERE (((Transaktioner.Datum) Like '2009-12*')) 
GROUP BY Transaktioner.Typ;
UNION ALL
SELECT Transaktioner.Typ, Sum(Transaktioner.Belopp)  AS 2010Jan
FROM Transaktioner 
WHERE (((Transaktioner.Datum) Like '2010-01*')) 
GROUP BY Transaktioner.Typ;

The result is Two Columns 'Typ' and '2009Dec'. The result from the second select statements comes in '2009Dec' below the result from the first select statement.

I would like three columns: Typ, 2009Dec and 2010Jan. Is it possible to solve?

Thanks! /xhist

xhist
Starting Member

3 Posts

Posted - 2010-05-31 : 01:51:28

Hmm, what I would like to do is FULL JOIN but by using select statements instead of tables.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-31 : 02:51:04

Try This -


SELECT		Typ, SUM(2009Dec), Sum(2010Jan)
(
	SELECT	Transaktioner.Typ,
		CASE WHEN Transaktioner.Datum Like '2009-12*' THEN Transaktioner.Belopp ELSE 0 END AS 2009Dec,
		CASE WHEN Transaktioner.Datum Like '2010-01*' THEN Transaktioner.Belopp ELSE 0 END AS 2010Jan
	FROM	Transaktioner
)
GROUP BY	Typ

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

xhist
Starting Member

3 Posts

Posted - 2010-05-31 : 05:32:12

Thanks!! It didn't work :D

Well this is how it looks like now.


SELECT		 Transaktioner.Typ, Sum(2009Dec), Sum(2010Jan)
(
	SELECT	 Transaktioner.Typ,
		CASE WHEN Transaktioner.Datum Like '2009-12*' THEN Transaktioner.Belopp ELSE 0 END AS 2009Dec,
		CASE WHEN Transaktioner.Datum Like '2010-01*' THEN Transaktioner.Belopp ELSE 0 END AS 2010Jan
	FROM	 Transaktioner;
)
GROUP BY	 Transaktioner.Typ;

I get the following error message: Syntax error (missing operator) in query expression 'Sum(2009Dec)'.

I did some google and realized that CASE is not supported in Access. But I could use the llf-function instead.

http://www.wiredbox.net/Forum/Thread297207_how_to_use_SQL_case_statement_in_acess_query.aspx

Unsure about the ';' sign as well

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-31 : 08:18:33

Post your Question at ACCESS Forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources