none
Count Aggregation

    Question

  • Hi ALL ,

    I have a view  that results in the below Output which is based of a very complex length query :

    Action LengthMinutes
    ADD 37
    ADD 37
    ADD 25
    Delete 1
    COPY 1
    COPY 3
    COPY 1
    CUT 2
    ADD 24
    ADD 37
    ADD 62
    ADD 50

    I wish the output in the below mentioned format :

    Here the minutes are grouped as Columns with count of Actions as Value .

    Kindly Help

    ACTION 0-30min 30-60min 60 + min
    ADD 2 4 1
    Delete 1 0 0
    COPY 3 0 0

    Sunday, April 13, 2014 8:15 AM

Answers

  • select action,

    count(case when lengthminutes  between 0 and 30 then 1 end) [0-30 min],

    count(case when lengthminutes  between 30 and 60 then 1 end) [30-60 min],

    count(case when lengthminutes  >60  then 1 end) [60+ min]

    from tbl group by action


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Priya Bange Sunday, April 13, 2014 8:45 AM
    Sunday, April 13, 2014 8:23 AM
  • this is another way using PIVOT operator

    declare @t table
    (
    [Action] varchar(100),
    LengthInMinutes int
    )
    insert @t
    values('ADD',	 37),
    ('ADD',	 37),
    ('ADD',	 25),
    ('Delete',	 1),
    ('COPY',	 1),
    ('COPY',	 3),
    ('COPY',	 1),
    ('CUT',	 2),
    ('ADD',	 24),
    ('ADD',	 37),
    ('ADD',	 62),
    ('ADD',	 50)
    
    SELECT *
    FROM
    (
    SELECT [Action],
    CASE 
    WHEN LengthInMinutes BETWEEN 0 AND 30 THEN '0 - 30min'
    WHEN LengthInMinutes BETWEEN 31 AND 60 THEN '30 - 60min'
    WHEN LengthInMinutes > 60 THEN '60+ min'
    END AS Category
    FROM @t
    )t
    PIVOT(COUNT(Category) FOR Category IN ([0 - 30min],[30 - 60min],[60+ min]))p


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Sunday, April 13, 2014 9:20 AM
    • Marked as answer by Priya Bange Sunday, April 13, 2014 9:35 AM
    Sunday, April 13, 2014 9:16 AM

All replies

  • select action,

    count(case when lengthminutes  between 0 and 30 then 1 end) [0-30 min],

    count(case when lengthminutes  between 30 and 60 then 1 end) [30-60 min],

    count(case when lengthminutes  >60  then 1 end) [60+ min]

    from tbl group by action


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Priya Bange Sunday, April 13, 2014 8:45 AM
    Sunday, April 13, 2014 8:23 AM
  • Have a look at the unpivot tsql function. That wil help you get the minutes on the columns, as for the grouping use a case statement in the group by clause.
    Sunday, April 13, 2014 8:24 AM
  • this is another way using PIVOT operator

    declare @t table
    (
    [Action] varchar(100),
    LengthInMinutes int
    )
    insert @t
    values('ADD',	 37),
    ('ADD',	 37),
    ('ADD',	 25),
    ('Delete',	 1),
    ('COPY',	 1),
    ('COPY',	 3),
    ('COPY',	 1),
    ('CUT',	 2),
    ('ADD',	 24),
    ('ADD',	 37),
    ('ADD',	 62),
    ('ADD',	 50)
    
    SELECT *
    FROM
    (
    SELECT [Action],
    CASE 
    WHEN LengthInMinutes BETWEEN 0 AND 30 THEN '0 - 30min'
    WHEN LengthInMinutes BETWEEN 31 AND 60 THEN '30 - 60min'
    WHEN LengthInMinutes > 60 THEN '60+ min'
    END AS Category
    FROM @t
    )t
    PIVOT(COUNT(Category) FOR Category IN ([0 - 30min],[30 - 60min],[60+ min]))p


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Sunday, April 13, 2014 9:20 AM
    • Marked as answer by Priya Bange Sunday, April 13, 2014 9:35 AM
    Sunday, April 13, 2014 9:16 AM