Two select statements same table

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

Transact-SQL (2005)

Two select statements same table

Author

Topic

Martin.Bergius
Starting Member

3 Posts

Posted - 2014-08-21 : 02:56:36

Hi,
I'm trying to get customerno,salestotal and profittotal from each customer from same table in same query.

I have no problem to get the values one by one like this(in two different queries):

Query 1:

SELECT AcTr.R4, SUM(AcAm*-1)
FROM AcTr
WHERE (AcTr.AcYr='2013') AND (AcTr.AcPr='11') AND (AcTr.AcNo>='3000' And AcTr.AcNo<='3999')
GROUP BY AcTr.R4
Result:
CustId SalesTotal(SUM(AcAm*-1)
CustA 100.000
CustB 200.000
CustC 100.000

Query 2:

SELECT AcTr.R4, SUM(AcAm*-1)
FROM AcTr
WHERE (AcTr.AcYr='2013') AND (AcTr.AcPr='11') AND (AcTr.AcNo>='3000' And AcTr.AcNo<='8999')
GROUP BY AcTr.R4
Result:
CustId ProfitTotal(SUM(AcAm*-1)
CustA 10.000
CustB 20.000
CustC 10.000

As you can see the only difference is the accounts in the where clause.

Is it possible to have everything in one query and get a result like this:

Customer SalesTotal ProfitTotal
CustA x y
CustB x y
CustC x y

Thanks in advance

/Martin

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 03:03:04

[code]
SELECT AcTr.R4
, SUM(CASE WHEN AcTr.AcNo<='3999' THEN AcAm*-1 ELSE 0 END) as SalesTotal
, SUM(AcAm*-1) asa ProfitTotal
FROM AcTr
WHERE (AcTr.AcYr='2013') AND (AcTr.AcPr='11') AND (AcTr.AcNo>='3000' And AcTr.AcNo<='8999')
GROUP BY AcTr.R4
[/code]

sabinWeb MCP

Martin.Bergius
Starting Member

3 Posts

Posted - 2014-08-21 : 04:37:16

ok, thanks.
And if i want to include another table with conditions?
I want to include customername ( Actor.Nm ) and want to get only the customers that are registrered/created 2013.(Actor.CreDt>='20130101')

Tried with:

SELECT AcTr.R4, Actor.Nm, SUM(CASE WHEN AcTr.AcNo<='3999' THEN AcAm*-1 ELSE 0 END) , SUM(AcAm*-1)
FROM F0001.dbo.AcTr AcTr, F0001.dbo.Actor Actor
WHERE (Actor.CustNo = AcTr.R4) AND (Actor.CreDt>='20140101') AND (AcTr.AcYr='2013') AND (AcTr.AcNo>='3000' AND AcTr.AcNo<='8999')
GROUP BY AcTr.R4

But getting error msg: ( Wrong columnexpression('SUM(CASE WHEN AcTr.AcNo<='3999' THEN AcAm*-1 ELSE 0 END)'

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 04:43:04

[code]
SELECT AcTr.R4
,Actor.Nm
,SUM(CASE WHEN AcTr.AcNo <= '3999' THEN AcTr.AcAm*-1 ELSE 0 END)
,SUM(AcTr.AcAm*-1)

FROM F0001.dbo.AcTr AcTr, F0001.dbo.Actor Actor
WHERE (Actor.CustNo = AcTr.R4) AND (Actor.CreDt>='20140101') AND (AcTr.AcYr='2013') AND (AcTr.AcNo>='3000' AND AcTr.AcNo<='8999')
GROUP BY AcTr.R4
,Actor.Nm
[/code]

What is the relation between AcTr and Actor ? the join field ?

Can you post some sample data ? for testing ...

sabinWeb MCP

Martin.Bergius
Starting Member

3 Posts

Posted - 2014-08-21 : 05:19:28

Many Thanks. Actor.Nm in Group was the solution ;-)

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-21 : 07:17:06

Your welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources