CODEWITHSUNDEEP
sql-server
sm86
sm86

Reputation: 87

SQL Server query results sort order with totals

I am trying to sort by the ID with the highest total. I can sort by ID and then get the total for each member but I cannot seem to get it to sort by id and total.

Below is what I have and what I want. On what I have I am doing

ORDER BY 
    1, 
    CASE WHEN DIAGN1 = 'TOTAL' THEN 'Z' END

Any suggestions on what I should do? Thanks.

WITH F3 AS
(
SELECT F2.ID
,F2.DIAGN1 
,F2.PROVIDER_NAME
,F2.FROM_DATE
,F2.DATE_TO
,F2.UNITS
,F2.TOTAL_PAID 
,F2.PER_DIEM
,F2.DW_PAID_DATE
FROM F2

UNION ALL

SELECT ID
,NULL DIAGN1 
,NULL PROVIDER_NAME
,NULL FROM_DATE
,NULL DATE_TO
,SUM(UNITS) AS UNITS
,SUM(TOTAL_PAID) AS TOTAL_PAID
,NULL PER_DIEM
,NULL DW_PAID_DATE
FROM F2
GROUP BY ID
)

,F4 AS
(
SELECT F3.ID
,CASE WHEN F3.DIAGN1 IS NULL THEN 'TOTAL' ELSE F3.DIAGN1 END AS DIAGN1
,F3.PROVIDER_NAME
,F3.FROM_DATE
,F3.DATE_TO
,F3.UNITS
,FORMAT(F3.TOTAL_PAID,'C','EN-US') AS TOTAL_PAID
,FORMAT(F3.PER_DIEM,'C','EN-US') AS PER_DIEM
,F3.DW_PAID_DATE 
FROM F3
)

SELECT F4.ID
,F4.DIAGN1
,F4.PROVIDER_NAME
,F4.FROM_DATE
,F4.DATE_TO
,F4.UNITS
,F4.TOTAL_PAID
,F4.PER_DIEM
,F4.DW_PAID_DATE 
FROM F4
ORDER BY 1, CASE WHEN DIAGN1 = 'TOTAL' THEN 'Z' END,3,4

enter image description here

Upvotes: 1

Views: 83

Answers (1)

Charlieface
Charlieface

Reputation: 72087

I think this will give you what you want.

  • We use GROUPING SETS to get the individual rows and the rolled-up SUM
  • GROUPING(F2.DIAGN1) is 1 for the roll-up row only, in this case you can use any column
  • Any column not in an aggregation returns NULL for the rolled-up row
  • We order by a windowed SUM, you need to make sure to sum the sum, and partition also by GROUPING. The we order by ID, then by GROUPING
SELECT
     F2.ID
    ,CASE WHEN GROUPING(F2.DIAGN1) = 1 THEN 'TOTAL' ELSE F2.DIAGN1 END AS DIAGN1
    ,F2.PROVIDER_NAME
    ,F2.FROM_DATE
    ,F2.DATE_TO
    ,SUM(F2.UNITS) UNITS
    ,FORMAT(SUM(F2.TOTAL_PAID),'C','EN-US') AS TOTAL_PAID
    ,FORMAT(F2.PER_DIEM,'C','EN-US') AS PER_DIEM
    ,F2.DW_PAID_DATE
FROM F2
GROUP BY GROUPING SETS (
  (
     F2.ID
    ,F2.DIAGN1 
    ,F2.PROVIDER_NAME
    ,F2.FROM_DATE
    ,F2.DATE_TO
    ,F2.UNITS
    ,F2.TOTAL_PAID 
    ,F2.PER_DIEM
    ,F2.DW_PAID_DATE
  ),
  (
     F2.ID
  )
)
ORDER BY
  SUM(SUM(F2.TOTAL_PAID)) OVER (PARTITION BY ID, GROUPING(F2.DIAGN1)) DESC
, ID
, GROUPING(F2.DIAGN1)
, PROVIDER_NAME
, FROM_DATE;

db<>fiddle

Upvotes: 3

Related Questions