By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,290 Members | 1,653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,290 IT Pros & Developers. It's quick & easy.

Calculating percentage with subquery

P: 1
HI,

I have a 20000 records table containing (at least) these columns:

SAMPLE DATA
Expand|Select|Wrap|Line Numbers
  1.  week   process_type         Group           in SLA  Importance     Active
  2. 201142  Change Management    IN.Intel.Central  1     Production       1
  3. 201142  Incident Management  IN.Intel.Central  0     Small Change     1
  4. 201143  Incident Management  IN.Intel.Central  1     3                0
  5. 201143  Incident Management  IN.Unix.HP        1     2                0
  6. 201144  Change Management    IN.Unix.HP        1     Standard Change  1
  7. 201144  Change Management    IN.Unix.HP        0     Standard Change  1
  8. 201144  Change Management    IN.Unix.HP        1     Standard Change  1
I do a grouped select count:

Expand|Select|Wrap|Line Numbers
  1. SELECT   Sheet1.week
  2.        , Sheet1.process_type
  3.        , Sheet1.Group
  4.        , Sheet1.[in SLA]
  5.        , Sheet1.Importance
  6.        , Sheet1.Active
  7.        , Count(Sheet1.ID) AS CountOfID
  8. FROM     Sheet1
  9. GROUP BY Sheet1.week
  10.        , Sheet1.process_type
  11.        , Sheet1.Group
  12.        , Sheet1.[in SLA]
  13.        , Sheet1.Importance
  14.        , Sheet1.Active
  15. HAVING   (((Sheet1.process_type)="Change Management")
  16.    AND   ((Sheet1.[in SLA])=0)
  17.    AND   ((Sheet1.Active)=0));
Giving me a grouped summary of the amount of tickets per week, group and type, where the main criteria is "in SLA" = 0 returning something like this:

Expand|Select|Wrap|Line Numbers
  1.  week   process_type       Group          in SLA  Active  CountOfID
  2. 201142  Change Management  IN.Unix.HP       0     0       2
  3. 201143  Change Management  IN.Unix.HP       0     0       2
  4. 201143  Change Management  IN.Windows.COD   0     0       1
  5. 201143  Change Management  PL.AppsMgmt.ANG  0     0       1
  6. 201143  Change Management  PL.AppsMgmt.     0     0       1
  7. 201143  Change Management  PL.Intel         0     0       1
  8. 201144  Change Management  IN.Intel.South   0     0       2
Now I want an additional column filled with the count of the total for the same criteria's but now also for tickets in sla ("in sla" = 1 or " in sla" = 0)

With this additional result I can calcultate the percentage of tickets that are out of SLA (out of SLA = too late) for the total amount of tickets per week, group and type...

How can I incorporate the returned values of a record in a subquery to count and return the totals within the same record...???

Any help is appreciated...

Thansk,
Jeroen

ps Created this post in a text editor, copying and pasting in notepad maybe restores the readability of the sample data :-)
Nov 15 '11 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,421
You could do an aggregate query to get the total and then join that to an aggregate query to get your out of sla total. That will give you both fields in one line so you calculate your percentage.
Nov 15 '11 #2

NeoPa
Expert Mod 15k+
P: 31,707
I fixed the layout for you Jeroen. You did a good job, but for tabular data you need to use the [ CODE ] tags (no spaces) and no tabs (Use multiple spaces instead).

As side-issues (Rabbit has already given an answer that fits) I would say that it's a better idea with count fields to :
  1. Name them more usefully (EG. [NumRecs] or [RecCount]).
  2. Count the whole record rather than an individual field Count(*).

As an illustration, your SQL code could have looked like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Week]
  2.        , [Process_Type]
  3.        , [Group]
  4.        , [in SLA]
  5.        , [Importance]
  6.        , [Active]
  7.        , Count(*) AS NumRecs
  8. FROM     [Sheet1]
  9. WHERE   (([Process_Type] = 'Change Management')
  10.    AND   ([in SLA] = 0)
  11.    AND   ([Active] = 0))
  12. GROUP BY [Week]
  13.        , [Process_Type]
  14.        , [Group]
  15.        , [in SLA]
  16.        , [Importance]
  17.        , [Active]
Nov 16 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.