Calculating percentage with subquery

[Jeroen van de V]

HI,

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

SAMPLE DATA

 week   process_type         Group           in SLA  Importance     Active
201142  Change Management    IN.Intel.Central  1     Production       1
201142  Incident Management  IN.Intel.Central  0     Small Change     1
201143  Incident Management  IN.Intel.Central  1     3                0
201143  Incident Management  IN.Unix.HP        1     2                0
201144  Change Management    IN.Unix.HP        1     Standard Change  1
201144  Change Management    IN.Unix.HP        0     Standard Change  1
201144  Change Management    IN.Unix.HP        1     Standard Change  1

I do a grouped select count:

SELECT   Sheet1.week
       , Sheet1.process_type
       , Sheet1.Group
       , Sheet1.[in SLA]
       , Sheet1.Importance
       , Sheet1.Active
       , Count(Sheet1.ID) AS CountOfID
FROM     Sheet1
GROUP BY Sheet1.week
       , Sheet1.process_type
       , Sheet1.Group
       , Sheet1.[in SLA]
       , Sheet1.Importance
       , Sheet1.Active
HAVING   (((Sheet1.process_type)="Change Management")
   AND   ((Sheet1.[in SLA])=0)
   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:

 week   process_type       Group          in SLA  Active  CountOfID
201142  Change Management  IN.Unix.HP       0     0       2
201143  Change Management  IN.Unix.HP       0     0       2
201143  Change Management  IN.Windows.COD   0     0       1
201143  Change Management  PL.AppsMgmt.ANG  0     0       1
201143  Change Management  PL.AppsMgmt.     0     0       1
201143  Change Management  PL.Intel         0     0       1
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 :-)

[Rabbit]

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.

[NeoPa]

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 :

SELECT   [Week]
       , [Process_Type]
       , [Group]
       , [in SLA]
       , [Importance]
       , [Active]
       , Count(*) AS NumRecs
FROM     [Sheet1]
WHERE   (([Process_Type] = 'Change Management')
   AND   ([in SLA] = 0)
   AND   ([Active] = 0))
GROUP BY [Week]
       , [Process_Type]
       , [Group]
       , [in SLA]
       , [Importance]
       , [Active]