July 22, 2009 at 6:28 am
How do I put these two select statement into one result set?
(Select Count(*) As SRIP FROM dbo.tblContracts C WHERE C.ACTION_CODE='SOLD_000003'
AND C.IT_Code = 'SRIP'
AND C.ContractStatus = 'A' AND C.UnitState = 'AZ'),
(Select Count(*) As TA FROM dbo.tblContracts C
WHERE C.ACTION_CODE='SOLD_000003'
AND CI.IT_Code = 'TA'
AND C.ContractStatus = 'A' AND C.UnitState = 'AZ')
July 22, 2009 at 6:30 am
Does this work?
select
(Select Count(*) As SRIP FROM dbo.tblContracts C WHERE C.ACTION_CODE='SOLD_000003'
AND C.IT_Code = 'SRIP'
AND C.ContractStatus = 'A' AND C.UnitState = 'AZ') as SRIP,
(Select Count(*) As TA FROM dbo.tblContracts C
WHERE C.ACTION_CODE='SOLD_000003'
AND CI.IT_Code = 'TA'
AND C.ContractStatus = 'A' AND C.UnitState = 'AZ') as Ta
July 22, 2009 at 6:33 am
I will try it.
Thanks
July 22, 2009 at 6:35 am
it did not work.
July 22, 2009 at 6:37 am
What result set are you wanting to get?
That should have returned 1 row with 2 columns
July 22, 2009 at 6:37 am
Another variation:
Select sum(case C.IT_Code when 'SRIP' then 1 else 0 end) as SRIP,
sum(case C.IT_Code when 'TA' then 1 else 0 end) as TA
FROM dbo.tblContracts C
WHERE C.ACTION_CODE='SOLD_000003'
AND C.ContractStatus = 'A' AND C.UnitState = 'AZ'
AND C.IT_Code in ('SRIP', 'TA')
/Kenneth
July 22, 2009 at 6:41 am
I will try the Sum Statement.
Thanks
July 22, 2009 at 6:46 am
It worked.
thank you very much.
July 22, 2009 at 7:51 am
I was trying to come up with something exactly like this before, but couldn't ever quite get it, so thanks!
What I had been doing (translated to this example) was essentially this:
SELECT
(SELECT COUNT(*) FROM dbo.tblContracts WHERE IT_Code = 'SRIP' AND ACTION_CODE = C.ACTION_CODE AND ContractStatus = C.ContractStatus AND UnitState = C.UnitState) AS SRIP,
(SELECT COUNT(*) FROM dbo.tblContracts WHERE IT_Code = 'TA' AND ACTION_CODE = C.ACTION_CODE AND ContractStatus = C.ContractStatus AND UnitState = C.UnitState) AS TA
FROM dbo.tblContracts
WHERE C.ACTION_CODE = 'SOLD_000003'
AND C.ContractStatus = 'A'
AND C.UnitState = 'AZ'
Obviously, in this example, it's a lot of overkill. But in mine, I was doing a GROUP BY and getting other aggregate data for the whole group as well as the counts for the things like IT_Code in this example.
But, I just tried the SUM and CASE in mine, and it works great as well! Also, it runs in about half the time.
July 22, 2009 at 8:01 am
Thanks again. your solution was great.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy