Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Counting parent records and displaying Total??

    We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:

    parameter # Calls
    ------------ --------
    desktop\pp 5
    desktop\qq 6 {This is what I am getting at this stage}
    desktop\tt 4
    network\rr 9
    network\gg 10
    software\vv 3

    This is what I would like to have:

    parameter # Calls
    ------------ -------
    desktop 15
    network 19
    software 3

    Please Help!

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    select p.*, c.*, (p.amt+c.amt) as "Total Amt"
    from parent p
    (select id, sum(amt) as amt
    from child c
    group by id) c ON =;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004


    Sorry, but I Forgot to mention that the second column is a count of the occurances in the 1st column. The query has to be run on only one field.

    I am attaching my query:

    SELECT workitem_category_tree_value,
    count(*) as '# Work Items'

    FROM dbo.workitem_detail_view hd1
    WHERE hd1.[workitem_is_last] = 1
    AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%')

    GROUP BY hd1.[workitem_category_tree_value]

    Then this result is diplayed:

    Desktop 8
    Desktop\Administration 12
    Desktop\Administration\Reset Password 42
    Desktop\Administration\Unlock Account 30
    Desktop\Notebook\LAN 1
    Desktop\Notebook\LAN\Join to Domain 2
    Desktop\Notebook\LAN\Network Connection 1
    Desktop\Notebook\RAS 1
    Desktop\Notebook\RAS\Configure 5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts