Transact SQL – STRING_AGG – Error – “Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.”

Background

Working on some Transact SQL code yesterday.

Ran into an error that was new to me.

Error

Error – Image

Error – Text


Msg 8711, Level 16, State 1, Line 80
Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.

Sample Code

The original code is a bit complex and sharing it will distract from this post’s point.

So here we go with sample code:-

 

Outline

  1. Original
    • The original code works
    • We have a column areaCode
    • The column also has a String_Agg
      • The String_Agg orders by the areaCode column
  2. Code – Error
    • The second code fails
    • The failure is because we added a new column, areaCodeAlt
    • The new column also has a String_Agg
      • The String_Agg orders by the id column
  3. Code – Remediative
    1. The third code works
    2. It separates out each String-Agg/”Within Group”  combination into its own common table expression ( cte )

Code

Code – Original

SQL

</pre>

set nocount on
go

declare @tblAreaCode TABLE
(

[id] int not null
identity(1,1)

, [stateOrRegion] varchar(100) not null

, [city] varchar(100) not null

, [areaCode] char(3) not null

, [yearEffected] int null

, [areaCodeAndyearEffected]
as case

when ( [yearEffected] is null) then [areaCode]

else
(
[areaCode]
+ ' ('
+ cast( [yearEffected] as varchar(10))
+ ' )'
)

end

, [yearEffectedAndAreaCode]
as case

when ( [yearEffected] is null) then [areaCode]

else
(
+ ' ('
+ cast( [yearEffected] as varchar(10))
+ ' - '
+ ' )'
)
+ ' '
+ [areaCode]

end

)

/*
https://en.wikipedia.org/wiki/List_of_area_code_overlays
*/
insert into @tblAreaCode
(
[stateOrRegion]
, [city]
, [areaCode]
, [yearEffected]

)
values

/*
https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008.
*/
( 'British Columbia', 'British Columbia', '604', null )
, ( 'British Columbia', 'British Columbia', '250', 1996 )
, ( 'British Columbia', 'British Columbia', '778', 2001 )

/*
https://en.wikipedia.org/wiki/Area_codes_678_and_470
*/
, ( 'Atlanta', 'Georgia', '404', null )
, ( 'Atlanta', 'Georgia', '678', 1998 )
, ( 'Atlanta', 'Georgia', '470', 2010 )

select
tblAC.[stateOrRegion]

, tblAC.[city]

, [areaCode]
= STRING_AGG
(
tblAC.[areaCode]
, ', '
)
WITHIN GROUP
(
ORDER BY
tblAC.[areaCode] ASC
)

from @tblAreaCode tblAC

group by

tblAC.[stateOrRegion]

, tblAC.[city]

order by

tblAC.[stateOrRegion]

, tblAC.[city]
<pre>

Image

Code – Error

SQL



set nocount on
go

declare @tblAreaCode TABLE
(

      [id] int not null
        identity(1,1)


    , [stateOrRegion]   varchar(100) not null

    , [city]            varchar(100) not null

    , [areaCode]        char(3) not null

    , [yearEffected]    int          null

    , [areaCodeAndyearEffected] 
                        as case

                                when ( [yearEffected] is null) then [areaCode]

                                else 
                                     (
                                          [areaCode]
                                        + ' (' 
                                        + cast( [yearEffected] as varchar(10)) 
                                        + ' )' 
                                    )
                                
                           end
            

    , [yearEffectedAndAreaCode] 
                        as case

                                when ( [yearEffected] is null) then [areaCode]

                                else 
                                     (
                                        + ' (' 
                                        + cast( [yearEffected] as varchar(10)) 
                                        + ' - '
                                        + ' )' 
                                    )
                                    + ' ' 
                                    + [areaCode]
                                
                           end
            

)

/*
    https://en.wikipedia.org/wiki/List_of_area_code_overlays
*/
insert into @tblAreaCode
(
      [stateOrRegion]
    , [city]     
    , [areaCode]
    , [yearEffected]

)
values

      /*
        https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008.
      */
      ( 'British Columbia', 'British Columbia', '604', null )
    , ( 'British Columbia', 'British Columbia', '250', 1996 )
    , ( 'British Columbia', 'British Columbia', '778', 2001 )

      /*
        https://en.wikipedia.org/wiki/Area_codes_678_and_470
      */
    , ( 'Atlanta', 'Georgia', '404', null )
    , ( 'Atlanta', 'Georgia', '678', 1998 )
    , ( 'Atlanta', 'Georgia', '470', 2010 )

/*
    Sample -02
*/
select 
          tblAC.[stateOrRegion]

        , tblAC.[city]

        , [areaCode]
            = STRING_AGG
              (
                  tblAC.[areaCode]
                , ', '
              )
              WITHIN GROUP 
              (
                ORDER BY 
                    tblAC.[areaCode] ASC
              ) 

        , [areaCodeAlt]
            = STRING_AGG
              (
                  tblAC.[areaCode]
                , ', '
              )
              WITHIN GROUP 
              (
                ORDER BY 
                    tblAC.[id] ASC
              ) 

from   @tblAreaCode tblAC

group by

          tblAC.[stateOrRegion]

        , tblAC.[city]



Image

Code – Sample 03

SQL


set nocount on
go

declare @tblAreaCode TABLE
(

      [id] int not null
        identity(1,1)


    , [stateOrRegion]   varchar(100) not null

    , [city]            varchar(100) not null

    , [areaCode]        char(3) not null

    , [yearEffected]    int          null

    , [areaCodeAndyearEffected] 
                        as case

                                when ( [yearEffected] is null) then [areaCode]

                                else 
                                     (
                                          [areaCode]
                                        + ' (' 
                                        + cast( [yearEffected] as varchar(10)) 
                                        + ' )' 
                                    )
                                
                           end
            

    , [yearEffectedAndAreaCode] 
                        as case

                                when ( [yearEffected] is null) then [areaCode]

                                else 
                                     (
                                        + ' (' 
                                        + cast( [yearEffected] as varchar(10)) 
                                        + ' - '
                                        + ' )' 
                                    )
                                    + ' ' 
                                    + [areaCode]
                                
                           end
            

)

/*
    https://en.wikipedia.org/wiki/List_of_area_code_overlays
*/
insert into @tblAreaCode
(
      [stateOrRegion]
    , [city]     
    , [areaCode]
    , [yearEffected]

)
values

      /*
        https://www.cbc.ca/news/canada/british-columbia/new-236-area-code-rolled-out-in-b-c-1.1358072#:~:text=Until%201996%2C%20604%20was%20the,across%20the%20province%20in%202008.
      */
      ( 'British Columbia', 'British Columbia', '604', null )
    , ( 'British Columbia', 'British Columbia', '250', 1996 )
    , ( 'British Columbia', 'British Columbia', '778', 2001 )


/*
    Sample -03
*/
; with cte01
as
(
    select 
              tblAC.[stateOrRegion]

            , tblAC.[city]

            , [areaCodeAlt]
                = STRING_AGG
                  (
                      tblAC.[areaCode]
                    , ', '
                  )
                  WITHIN GROUP 
                  (
                    ORDER BY 
                        tblAC.[id] ASC
                  ) 


            , [areaCodeAltYear]
                = STRING_AGG
                  (
                      tblAC.[areaCodeAndyearEffected] 
                    , ', '
                  )
                  WITHIN GROUP 
                  (
                    ORDER BY 
                        tblAC.[id] ASC
                  ) 

    from   @tblAreaCode tblAC

    group by

              tblAC.[stateOrRegion]

            , tblAC.[city]


)
select 
          tblAC.[stateOrRegion]

        , tblAC.[city]

        , [areaCode]
            = STRING_AGG
              (
                  tblAC.[areaCode]
                , ', '
              )
              WITHIN GROUP 
              (
                ORDER BY 
                    tblAC.[areaCode] ASC
              ) 

        , cte01.[areaCodeAlt]

        , cte01.[areaCodeAltYear]

from   @tblAreaCode tblAC

inner join cte01 

        on  tblAC.[stateOrRegion] = cte01.[stateOrRegion]

        and tblAC.[city] = cte01.[city]

group by

          tblAC.[stateOrRegion]

        , tblAC.[city]

        , cte01.[areaCodeAlt]

        , cte01.[areaCodeAltYear]



Image

Source Code Control

Github

DanielAdeniji / TransactSQLStringAgg
Link

 

Summary

Transact SQL’s String Aggregation function, STRING_AGG, is very useful.

It has some quicks.

It is a first iteration effort and it will only get better.

 

Referenced Work

  1. Wikipedia
    • List of area code overlays
      Link
    • Area codes 678 and 470
      Link
  2. CBC
    • British Columbia
      • New 236 area code rolled out in B.C.
        Link
  3. Microsoft
    • Docs
      • Docs / SQL / Reference / Transact-SQL (T-SQL) Reference / Functions / String / STRING_AGG
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s