SQL Counting values from joined tables

Question

Hi I have two Tables which i want to query from:

The first one is called bugs and has columns:

bg_id , bg_project , bg_reported_date

The second one is called Projects and has columns:

pj_id , pj_name , pj_parent_id

The bg_project of bugs represents the pj_id of the Projects.

I want to count how many bg_id i had in a single month for every parent project. What would the query should be to get results that would look like: ProjectParentName , Count

A parent project can't have another parent project.
If a project is a parent project then it will have the same pj_id as the pj_parent_id.
Here's an example:

pj_id   pj_name     pj_parent_id

1       Parent1     1
2       Child1      1

Thanks in advance

score 0 · Answer 1 · answered Mar 04 '13 at 06:28

0

Does this work for you? Semi-shot in the dark. Haven't done SQL in awhile.

SELECT COUNT(bugs.bg_project) AS BugCount,
       bugs.bg_reported_date AS BugReportDate,
       Projects.pj_name as ProjectParentName, 
       Projects.pj_id AS ProjectID 
FROM bugs
JOIN Projects
WHERE ProjectID = bugs.bg_project
AND (BugReportDate BETWEEN to_date('YEAR/2digitmonth/01','yyyy/mm/dd')
                   AND to_date('YEAR/2digitmonth/lastday','yyyy/mm/dd'));

answered Mar 04 '13 at 06:28

SomeShinyObject

7,581
6
39
59

Hi Christopher, thanks for your answer but i am afraid its not what i was looking for. Have a look on the comment i did above for NePh's answer. – Thanos Mar 04 '13 at 22:21
Are you using a [Nested Set Model](http://en.wikipedia.org/wiki/Nested_set_model) for this table? Perhaps you should look into a [different method for hierarchial structuring](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database) – SomeShinyObject Mar 12 '13 at 23:13

NePh · Answer 2 · 2013-03-19T16:00:05.657

0

This is how I would do it:

WITH    bugsInPeriod
          AS ( SELECT   *
               FROM     bugs
               WHERE    MONTH(bg_reported_date) = MONTH(GETDATE())
             )

    SELECT   pParent.pj_name AS ProjectParentName ,
             COUNT(bg_id) AS [Count]
    FROM     Projects pParent
             INNER JOIN Projects pChild ON pParent.pj_id = pChild.pj_parent_id
             LEFT JOIN bugsInPeriod b ON pChild.pj_id = b.bg_project
    WHERE    pParent.pj_id = pParent.pj_parent_id
    GROUP BY pParent.pj_name
    ORDER BY pParent.pj_name

Replace the MONTH(GETDATE()) with the desired month number, e.g. 2 if you want February.
The ORDER BY is optional.

In order to display projects with no bugs as well we need the LEFT JOIN. The WHERE-clause has to be in a subselect, otherwise it would eliminate the projects with no bugs.

Edit: I updated the query. I think it should now get the desired results.

edited Mar 19 '13 at 16:00

answered Mar 04 '13 at 07:51

NePh

966
9
21

Hi NePh, thanks for your answer but it is not exactly what i need. My problem comes with the parent project name. Let me give you an example. Lets say we have 1 parent project that has 2 child projects. This will be in the projects table as: 1, parent name , 10 || 2, child 1 name , 10 || 3, child 2 name , 10 . So the problem is that i need to count all the bugs under a parent project ( which includes ofc all the childs too ) and display the parent name next to the count. – Thanos Mar 04 '13 at 22:16
Oh I see, I misunderstood your question. Can a parent project have parent project? If yes is there a limit of levels? And which value is in pj_parent_id if thers is no parent? NULL I guess? – NePh Mar 05 '13 at 07:42
Hi NePh, No a parent project can't have an other parent project. If a project is a parent project then it will have the same pj_id as the pj_parent_id. So if a project doesn't have any chield projects it will automatically be a parent project (of it self). Hope this makes sense. Thanks for your help btw. – Thanos Mar 06 '13 at 01:53

SQL Counting values from joined tables

2 Answers2