none
sql query result with count istruction RRS feed

  • Question

  • I can't understand because this SQL query doesn't work:

    SELECT COUNT Department,
    IF(Department = 'toys', COUNT(), 0) AS numberOfToys,
    IF(Department = 'health', COUNT(
    ), 0) AS numberOfHealth
    FROM TABLE;


    TABLE

    Department - Value
    toys - A
    toys - B
    toys - C
    health - K
    health - F
    toys - G
    toys - R
    toys - W
    toys - Q

    I'd like to count number of occurrences about both toys record and health ones into 2 columns.

    department numberOfToys numberOfHealth

    toys 7 0
    health 0 2

    WHY ?!

    thanks


    rc

    Friday, May 24, 2019 1:25 PM

All replies

  • You can not use an IF Statement within a DML command, you have to use CASE (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, May 24, 2019 1:31 PM
  • select
    department, count(*) as cnt
    from table
    group by department
    order by department;

    Josh

    Friday, May 24, 2019 3:47 PM
  • Select Department, 
      Sum(Case When Department = 'toys' Then 1 Else 0 End) As NumberOfToys,
      Sum(Case When Department = 'health' Then 1 Else 0 End) As NumberOfHealth
    From Table
    Group By Department;
    

    Tom
    Friday, May 24, 2019 5:29 PM
  • Hi RoulVeranda,

     

    Please try following script.

    IF OBJECT_ID('table') IS NOT NULL drop table [table]
    go 
    create table [table] 
    (Department  varchar(20),
    Value  varchar(20) )
    insert into [table] values 
    ('toys','A'),
    ('toys','B'),
    ('toys','C'),
    ('health','K'),
    ('health','F'),
    ('toys','G'),
    ('toys','R'),
    ('toys','W'),
    ('toys','Q')
    
    select Department,
    sum(case when  Department = 'toys' then 1 else 0 end) numberOfToys,
    sum(case when  Department = 'health' then 1 else 0 end) numberOfHealth
    from [table]
    group by Department
    /*
    Department           numberOfToys numberOfHealth
    -------------------- ------------ --------------
    health               0            2
    toys                 7            0
    */
    ;with cte as(
    select Department,count(*) as ct
    from [table]
    group by Department
    )
    select Department,
    case when  Department = 'toys' then ct else 0 end numberOfToys,
    case when  Department = 'health' then ct else 0 end numberOfHealth
    from cte
    /*
    Department           numberOfToys numberOfHealth
    -------------------- ------------ --------------
    health               0            2
    toys                 7            0
    */

     

    Hope it can help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 27, 2019 5:44 AM
  • Hi,

    create table #mytable (Department varchar(50), Value varchar(50))
    insert #mytable values ('toys','A'),('toys','B'),('toys','C'),('health','K'),('health','F'),('toys','G'),('toys','R'),('toys','W'),('toys','Q')

    select Department, sum(iif(department = 'toys',1,0)) numberOfToys, sum(iif(department = 'health',1,0)) numberOfHealth
    from #mytable
    group by Department

    Mark as answer if it helps. Thanks.

    Monday, May 27, 2019 6:33 AM