CODEWITHSUNDEEP
sql
TomSla
TomSla

Reputation: 1

SQL summary from two weeknum columns

Is possible write a SQL Query to get the summary results below..?

Table preview:

UniqueID     StartWk     FinishWk
ID000001     2016|49     2016|49
ID000002     2016|49     2016|50
ID000003     2016|49     NULL
ID000004     2016|49     2016|50
ID000005     2016|49     2016|50
ID000006     2016|50     NULL
ID000007     2016|50     NULL
ID000008     2016|50     2016|50
ID000009     2016|50     2016|51

Summary output:

Week         Started     Finished
2016|49      5           1
2016|50      4           4
2016|51      0           1

Upvotes: 0

Views: 56

Answers (3)

Faisal
Faisal

Reputation: 4765

Using xQbert Query i am updating query like below.

SELECT 
    A.`Week`, 
    COALESCE(B.Started,0)Started, 
    COALESCE(C.Finished,0)Finished
FROM (SELECT StartWk as `Week` FROM my_table_name UNION 
      SELECT FinishWk as `Week` FROM my_table_name ) A
LEFT JOIN (SELECT startWk, count(1) Started
           FROM my_table_name
           GROUP BY StartWk) B
  ON A.`Week` = B.StartWk
LEFT JOIN (SELECT FinishWk, count(1) Finished
           FROM my_table_name
           GROUP BY FinishWk) C
  ON A.`Week` = C.FinishWk
WHERE TRIM(A.`Week`) != ''
ORDER BY A.`Week`;

SQL Fiddle Demo

Output:

enter image description here

Upvotes: 0

xQbert
xQbert

Reputation: 35343

This is one approach but I don't believe it's the most efficient.

First we need a set of all the weeks because you could have a finish week w/o a start or vice versa in your data.

Then we simply use a query to select the count for each weekly value.

SELECT A.week
    , (SELECT count(1) FROM table B WHERE B.StartWk = A.Week) as Started
    , (SELECT count(1) FROM table C WHERE C.FinishWk= A.Week) as Finished

FROM (SELECT startWk as Week FROM Table WHERE startWk is not null UNION 
      SELECT FinishWk as Week FROM Table WHERE FinishWk is not null
      ) A

This would likely be more efficient as it's able to get the counts once for each week instead of having to execute 2 queries for every week in the combined weeks.

SELECT A.Week, B.Started, C.Finished
FROM (SELECT startWk as Week FROM Table WHERE startWk is not null UNION 
      SELECT FinishWk as Week FROM Table WHERE FinishWk is not null) A
LEFT JOIN (SELECT startWk, count(1) Started
           FROM TABLE
           GROUP BY StartWk) B
  ON A.Week = B.startWk
LEFT JOIN (SELECT FinishWk, count(1) Finished
           FROM TABLE
           GROUP BY FinishWk) C
  ON A.Week = C.FinishWk

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

If your DBMS is ANSI compliant, below should work for you:

select
    coalesce(a.week, b.week) week,
    coalesce(a.cnt, 0) started,
    coalesce(b.cnt, 0) finished
from 
    (select startwk week, count(1) cnt from t where startwk is not null group by startwk) a
full outer join
(select finishwk week, count(1) cnt from t where finishwk is not null group by finishwk) b
on a.week = b.week
order by week;

Upvotes: 1

Related Questions