Post reply

Count of records with different conditions in the same select statement

  • July 22, 2009 at 6:28 am

    #133206

    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

    #1028246

    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

    #1028249

    I will try it.

    Thanks

  • July 22, 2009 at 6:35 am

    #1028254

    it did not work.

  • July 22, 2009 at 6:37 am

    #1028256

    What result set are you wanting to get?

    That should have returned 1 row with 2 columns

  • July 22, 2009 at 6:37 am

    #1028258

    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

    #1028260

    I will try the Sum Statement.

    Thanks

  • July 22, 2009 at 6:46 am

    #1028262

    It worked.

    thank you very much.

  • July 22, 2009 at 7:51 am

    #1028320

    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

    #1028324

    Thanks again. your solution was great.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply