I want to write a summary query and present the results in a single table

sql, sql-server, ssms, t-sql

Solution

No need for union.

Use WHERE to filter to only the status_keys that you want, then expand you CASE statement to re-code from a number to a word.

select
  case when status_key = '2' then 'Paid'
       when status_key = '1' then 'Queued'
       when status_key = '4' then 'Hold'
                             else 'Error!' end     AS [Status],
  COUNT(BillNo)                                    AS [Count],
  SUM(amtpd)                                       AS [Amount Paid]
from
  billtable
where
  client = 101
  AND status_key IN ('1','2','4')
group by
  status_key

EDIT Modified example using a dimension table

select
  status.description                                    AS [Status],
  COUNT(bill_table.BillNo)                              AS [Count],
  SUM(bill_table.amtpd)                                 AS [Amount Paid]
from
  billtable
inner join
  status
    on billtable.status_key = status.key
where
      bill_table.client      = 101
  AND bill_table.status_key IN ('1','2','4')
group by
  status.description

You can then have a foreign key constraint from `status` to `billtable`. This will ensure that data can not be inserted into `billtable` unless there is a corresponding key in `status`.

Your lookups will then always work. But at the 'cost' of inserts failing if the `status` table has not been correctly populated.

This `fact-table` and `dimension-table` construction is the underpinning of relational database design.

Problem

I need to write a summary report on 3 different status values, with a count and an amount column for each status, with the results presented in a single table. For example, the output would look like this: The query to produce each line of code (in an individual output) is: ``` select case when status_key = '2' then 'Paid' else '' end as 'Status' , COUNT(BillNo) as [Count] , SUM(amtpd) as [Amount Paid] from billtable where client = 101 and status_key = '2' group by status_key select case when status_key = '1' then 'Queued' else '' end as 'Status' , COUNT(BillNo) as [Count] , SUM(amtpd) as [Amount Paid] from billtable where client = 101 and status_key = '1' group by status_key select case when status_key = '4' then 'Hold' else '' end as 'Status' , COUNT(BillNo) as [Count] , SUM(amtpd) as [Amount Paid] from billtable where client = 101 and status_key = '4' group by status_key ``` This produces three results like: I am using SQL Server database and SSMS to develop the query.

Original source