locked
Different Count RRS feed

  • Question

  • User954998566 posted

    Hi, 

    I have a view as below

    id          month            year           service
    ----------------------------------------------------
    4855        4                2010           SERVICE1
    4855        4                2010           SERVICE1
    4855        4                2010           SERVICE2
    4866        5                2011           SERVICE1
    4866        5                2011           SERVICE2
    4866        5                2011           SERVICE2
    4866        6                2011           SERVICE2
    4866        5                2012           SERVICE2

    How can i add two columns : CountService1 and CountService2 in order to have

    id          month            year           service                 CountService1             CountService2
    ------------------------------------------------------------------------------------------------------------
    4855        4                2010           SERVICE1                 2                               1
    4866        5                2011           SERVICE1                 1                               2
    4866        6                2011           SERVICE2                 0                               1
    4866        5                2012           SERVICE2                 0                               1

    The idea is to count distinct service fro same (id,month,year)

    Thanks

    Friday, July 18, 2014 5:41 PM

Answers

  • User-1716253493 posted

    create two view then UNION them then sum them

    1. service1 only, set service2 col zero

    Select month, year, count(id) as CountService1, 0 as CountService2 from tbl where service="service1" ...  

    2. service2 only, set service1 col zero

    Select month, year, 0 as CountService1, count(id) as CountService2 from tbl where service="service2" ...  

    Use UNION ALL then SUM them (service1, service2)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 18, 2014 10:09 PM