How to Build a Summary by Joining to a Single Table with SQL Server

How do I build a summary by joining to a single table with SQL Server?

Summarize data using rollup:

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

What version of sql server are you using ?

If you dont have want to use rollup this should help:

SELECT
      FileName,
      SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS CountOf1,
      SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) AS CountOf2,
      SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) AS CountOf3
FROM
      MyTable
GROUP BY FileName
ORDER BY FileName

One to many table joined to summary table

;With cteTransactions As
(
Select *, Row_Number() Over (Partition By IdThatLinksToTransAndLedger, Order By
    ColumnToGivePriorityOnSelectingOneTransaction) SortOrder
   From Transactions
)  
Select *
   From LedgerSummary L
   Join (Select * From cteTransactions Where SortOrder = 1) T 
      On L.IdThatLinksToTransAndLedger = T.IdThatLinksToTransAndLedger

IdThatLinksToTransAndLedger - You need a key for Person
ColumnToGivePriorityOnSelectingOneTransaction - Priority column, usually a date descending to give most recent.

Generate summary data based on configuration table in SQL Server

You can use aggregate functions:

select title ScoreBand,
  count(*) TotalNoPeople, 
  avg(p.score) AvgScore  
from scoreperson p
inner join scoreminmax m
  on p.score between m.minscore and m.maxscore
group by Title
order by cast(left(title, 2) as int) 

see SQL Fiddle with Demo

If you have no person in an existing range, you can us something like this:

select case when title is not null 
            then title
            else 'No Range' end ScoreBand,
  count(personid) TotalNoPeople, 
  avg(p.score) AvgScore  
from scoreperson p
left join scoreminmax m
  on p.score between m.minscore and m.maxscore
group by id, Title
order by id

see SQL Fiddle with Demo

edit #2, based on your comments you can use:

select m.title ScoreBand,
  count(p.personid) TotalNoPeople, 
  avg(p.score) AvgScore  
from scoreminmax m
left join scoreperson p
  on p.score between m.minscore and m.maxscore
group by m.id, m.Title
order by m.id;

see SQL Fiddle with Demo

Create the summary table SQL

Most obvius in my opinion for this case:

;with cteStock as
(
  select 
    p.ProductCode, 
    p.ProductName, 
    p.ProductStockClosingBalance,
    row_number() over(partition by p.ProductCode, p.ProductName order by p.ProductStockId asc) rn_asc,
    row_number() over(partition by p.ProductCode, p.ProductName order by p.ProductStockId desc) rn_desc,
  from ProductStockTemp p 
)
select 
  s.ProductCode, s.ProductName,
  sum(case when s.rn_asc = 1 then s.ProductStockClosingBalance else 0) as TypeA,
  sum(case when s.rn_desc = 1 and s.rn_asc != s.rn_desc then s.ProductStockClosingBalance else 0) as TypeB
from cteStock s
group by s.ProductCode, s.ProductName

but it's no good to have such a denormalized table with no info about period and names stored right here.

s.rn_asc != s.rn_desc - for the case when there is only one row.
Not sure about default zero but it's up to you and your task definition.

Joining Summary Tables in MYSQL

SELECT customer,SUM(source = 'a') cnta, SUM(source = 'b') cntb FROM
     (
       SELECT 'a' source,customer FROM customer_a
       UNION ALL
       SELECT 'b',customer FROM customer_b
     ) n
 GROUP  
    BY customer;

Make a summary report in SQL Server

SELECT 'Expenditure' AS TableName, SUM(Amount) AS Amount
FROM Expenditure
WHERE Amount IS NOT NULL
UNION ALL
SELECT 'Auxiliary' AS TableName, SUM(Amount) AS Amount
FROM auxiliary
WHERE Category IS NOT NULL AND Amount >0;

How to create a summary table with subheadings in SQL?

you can get the values you want by this query , but the way you want to show it , should be done in the UI in your app:

select Marking
       , Area 
       , Division 
       , sum(amount) filter (where status = 'Status1') Status1Amount
       , count(*)    filter (where status = 'Status1') Status1Count
       , sum(amount) filter (where status = 'Status2') Status2Amount
       , count(*)    filter (where status = 'Status2') Status2Count
       , sum(amount) TotalAmount
       , count(*) TotalCount
from final_output
where Marking = 'A1' --< you can remove where clause to get the result for all the markings
group by rollup( marking, Area , Division)

group by rollup gives you grand total as well.

Update table with summary by column key

Try this out.

declare @rooms table
(
depa_key int,
room_key int,
d1 int,
d2 int
)

insert into @rooms(depa_key, room_key, d1, d2)
select 1, null, null, null
union all
select 1, 1, 1, 1
union all
select 1, 1, 1, 1
union all
select 2, null, null, null
union all
select 2, 1, 5, 3
union all
select 2, 1, 7, 2
union all
select 3, null, null, null
union all
select 3, 1, 6, 9
union all
select 3, 1, 5, 8

UPDATE @rooms
SET d1 = a.d1, d2 = a.d2
FROM (
        SELECT depa_key, SUM(D1)d1, SUM(d2) d2
        FROM @rooms
        GROUP BY depa_key
    ) a
    JOIN @rooms r ON
        a.depa_key = r.depa_key
WHERE r.room_key IS NULL

SELECT *
FROM @rooms

---

Related Topics