Need Help to write a query

  • I have a table like this

    Untitled

    I need to write a query to get the following output

    Columns

    Tenor, 0, 0.5, 1, 1.5, 2, 2.5, 3, ....... , 10 (0 to 10)

    Sum of Count of  Col A and Col B where Col values are >= the abs values of the columns

    Untitled

     

    For 0 count all the abs values in Col A and Col B group by Tenor where abs(value) > 0

    For 0.5 count all the abs values in Col A and Col B group by Tenor where abs(value) > 0.5

     

    and so on. Output should be like the above table

  • If I understand correctly, this should work for you

    SELECT Tenor,
    COUNT(CASE WHEN ABS(ColA) > 0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 0 THEN ColB END) AS [0],
    COUNT(CASE WHEN ABS(ColA) > 0.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 0.5 THEN ColB END) AS [0.5],
    COUNT(CASE WHEN ABS(ColA) > 1.0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 1.0 THEN ColB END) AS [1.0],
    COUNT(CASE WHEN ABS(ColA) > 1.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 1.5 THEN ColB END) AS [1.5],
    COUNT(CASE WHEN ABS(ColA) > 2.0 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 2.0 THEN ColB END) AS [2.0],
    COUNT(CASE WHEN ABS(ColA) > 2.5 THEN ColA END) + COUNT(CASE WHEN ABS(ColB) > 2.5 THEN ColB END) AS [2.5]
    FROM myTable
    GROUP BY Tenor
    ORDER BY Tenor;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Yes the result is correct. Can we make the column more dynamic? For now we need till 10, tomorrow if we need till 20 we may have to write 20 more lines of code.

  • To make this dynamic have a look here

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Got it Thanks.

     

    DECLARE @query_string as NVARCHAR(MAX),

    @cols as NVARCHAR(MAX)

    with data

    as (

    select top 21 (row_number() over(order by (select null))-1)*0.5 as rnk

    from master..spt_values

    )

    ,cols_data

    as(

    select ',count(case when abs(a.col_a)>='+cast(rnk as varchar(10)) +' then 1 end) + '

    +'count(case when abs(a.col_b)>='+cast(rnk as varchar(10)) +' then 1 end) as ['+cast(rnk as varchar(10))+'] '+ CHAR(10) as col

    from data

    )

    select @cols = string_agg(col, ' ')

    from cols_data

    SET @query_string = 'SELECT a.col ' +CHAR(10)

    + @cols

    +' from t a '+CHAR(10)

    +'GROUP BY a.col'

    execute(@query_string)

  • >> from master..spt_values <<

    Referencing master db like that is a horrible idea, just stop doing it.  It's very easy instead to create your own inline table.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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