T-SQL recursion

Question

I have a set of data that looks like below

Name  Time   Perc   Group  Mode  Control  Cancelled
A     10:52  10.10   10     0      1         0
B     09:00  10.23   10     1      1         1
C     12:02  12.01   12     0      1         1
D     10:45  12.12   12     1      7         1
E     12:54  12.56   12     1      3         0
F     01:01  13.90   13     0      11        1
G     02:45  13.23   13     1      12        1
H     09:10  13.21   13     1      1         0

I need an output like below;

Group  Perc   Cancelled
 10    20.33     1
 12    36.69     2
 13    40.34     2

What I'm getting was something like;

Group  Perc   Cancelled
 10    20.33     5
 12    36.69     5
 13    40.34     5

I don't know what to call this, I have something in my mind to call it like CTE?, but I really can't figure it out.

Here's my source;

 SELECT Group, SUM(Perc), Cancelled FROM
 (SELECT Group, Perc, (SELECT COUNT(*) FROM tblName WHERE Cancelled=1) AS Cancelled    FROM tblName WHERE 1=1 AND Group>=10)dt
 GROUP BY Group, Cancelled

Are you using a keyword as a column name in your real code? I saw "SELECT Group" and got confused. "GROUP BY Group" is bewildering. — Zec
– Zec, Commented Jul 11, 2013 at 2:40
just now, what i did was i created a #temp table then select'ed all the data first then left join the #temp table.. it does solve the issue, but i feel its a little bit lame.. — vpbot
– vpbot, Commented Jul 12, 2013 at 11:16

MatBailie · Accepted Answer · 2013-07-11 02:29:08Z

3

From your example, you don't need the nested query, any recursion, etc...

SELECT
  Group,
  SUM(Perc)        AS total_perc,
  SUM(cancelled)   AS total_cancelled
FROM
  tblName
WHERE
  1=1
  AND Group >= 10
GROUP BY
  Group

If you did have some different data, then you might want to use something like...

SUM(CASE WHEN cancelled > 0 THEN 1 ELSE 0 END)   AS total_cancelled

edited Jul 11, 2013 at 2:29

answered Jul 11, 2013 at 2:19

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hart CO Over a year ago

What's the purpose of having 1=1 in there?

MatBailie Over a year ago

@goatco - Copied from OPs code. Guessing it's to make building a dynamic where clause easier.

vpbot Over a year ago

my sample was to blame and who made it is no other that me, i forgot to show in the sample that before i can get the cancelled i need to validate the mode and control such as if mode=1 and control=1 then it is cancelled.. so can you please show me how?

MatBailie Over a year ago

@paulpolo - Do you mean that you don't have a cancelled field, but that a row should be counted as cancelled if mode > 0 AND control > 0? (You said = 1 in your comment, but in your data you have a row where control = 7, yet you treat that as cancelled?)

Collectives™ on Stack Overflow

T-SQL recursion

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related