SELECTING certain data 1

[ChrisMacPherson]

Hello all,

I would like to select these values from my two tables:

project title
no of bugs for project
no of open bugs for project (status = 1)

the tables I have look like this (lots left out):

projects
--------------------------------
pId
title
--------------------------------

bugs
--------------------------------
bId
status (binary)
--------------------------------

I know how to select all of the bugs for each project, but I dont know how to count the number of times a certain value appears in one of the columns.

Any pointers would be appreciated.



Chris MacPherson

 

[KarveR]

select count(somevalue) from table where something;

vague it is, in response to your unclear question.



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

most likely, there is a foreign key (in the "lots left out" part) from the bugs table back to the projects table, otherwise you wouldn't know which project a bug was associated with, and therefore you couldn't get any totals per project

select projecttitle
     , count(*)    as bugs
     , sum(case when status=1
                then 1 else 0 end)
                   as openbugs
  from projects
left outer
  join bugs
    on bugs.pID
     = projects.pID                     
group
    by projecttitle

r937.com | rudy.ca

 

[ChrisMacPherson]

@r937

Thanks for the quick reply.

Yes sorry, as you correctly guessed, there is a foreign key in the bugs table. As below (with some data too):

table projects
---------------------------
pId     title
 1     'project_one'
 2     'project_two'
----------------------------

table bugs
----------------------------
bId     pId     status
 1       1        0
----------------------------

This would represent one closed bug related to 'project_one'.

I tried the SQL you gave above and it seems to give almost what I want. The SUM bit I haven't used before but I understand that bit now.

The only problem is that I have only one entry in my bugs table and the result of the SQL tells me I have one bug in each of the two project entries??

This isn't right so far, but nearly

I tried this code (yours with a few corrections)

select projects.title
     , count(*)    as bugs
     , sum(case when status=1
                then 1 else 0 end)
                   as openbugs
  from projects
left outer
  join bugs
    on bugs.pID
     = projects.pID                     
group
    by projects.title

If using the data I gave at the top the given results are this:

---------------------------------
title            bugs    openbugs
'project_one'     1         0
'project_two'     1         0
---------------------------------

Chris MacPherson

 

[ChrisMacPherson]

Some extra information...

I just tried the same SQL as above but altered the bug status in the database so it was a '1'. like below

table bugs
----------------------------
bId     pId     status
 1       1        1
----------------------------

This gave me the following results

---------------------------------
title            bugs    openbugs
'project_one'     1         1
'project_two'     1         0
---------------------------------

This shows me that the openbugs column is correct as the 'project_two' value has zero. It is only the total bug count for each project that is failing.

I think the SQL is counting all the bugs in the database for each project instead of only it's own bugs.

Do you think this is right?

Chris MacPherson

 

[ChrisMacPherson]

I think I've solved it, not sure if it's the best method though. Below is my SQL:

select projects.title
     , sum(case when bugs.pId = projects.pId
                then 1 else 0 end)
                   as bugs
     , sum(case when status=1
                then 1 else 0 end)
                   as openbugs
  from projects
left outer
  join bugs
    on bugs.pID
     = projects.pID                     
group
    by projects.title

Based on your code so will give you a star.

Cheers


Chris MacPherson

 

[r937]

silly me, using count(*) in a left outer join

my bad

select projects.title
     , [b]count(bugs.pID)[/b]
                   as bugs
     , sum(case when status=1
                then 1 else 0 end)
                   as openbugs
  from projects
left outer
  join bugs
    on bugs.pID
     = projects.pID                     
group
    by projects.title

r937.com | rudy.ca

 

[ChrisMacPherson]

Ok, the above problem was sorted. Nice one I do however want to add one more column to the resulting table though. I'm not sure if this is possible as I have been trying to do it and researching joins for a while today now.

Anyway, here is what i want to add:

I have another table called members:

members
--------------
uId    pId
 1      1
 2      1
 3      2
--------------

Using this table I would like to count the number of users (uId) assigned to each project(pId) and add that column to the end of the previos problem.

I think this may be asking too much from one query, but I can't work it out myself yet so I thought I'd ask again.

I was thinking along the lines of what you did before:

SELECT
     projects.title, 
     count(bugs.pId) as bugs, 
     sum(case when status=1 
              then 1 else 0 end) as openBugs,
     count(members.pId) as mems
FROM projects
LEFT OUTER JOIN bugs USING (pId)
LEFT OUTER JOIN members USING (pId)
GROUP BY projects.title

Chris MacPherson

 

[r937]

can't do it with another join, because a single project can have many bugs, and a single project can have many members, so joining projects to both bugs and to members will introduce cross join effects

you can get around this is by introducing a correlated subselect in the SELECT clause

select projects.title
     , count(bugs.pID)
                   as bugs
     , sum(case when status=1
                then 1 else 0 end)
                   as openbugs
     , [b]( select count(*)
           from members
          where pId = projects.pId )
                   as  members[/b] 
  from projects
left outer
  join bugs
    on bugs.pID
     = projects.pID
group
    by projects.title

r937.com | rudy.ca

 

[ChrisMacPherson]

Thanks r937, That works fine.

Would you say this is an overcomplicated query now. Do you think it would be better programming to do this last issue with a new query?

I only ask as I am still a relative beginner in SQL and want to go the right way.

Thanks for your help.

Chris MacPherson

 

[r937]

Would you say this is an overcomplicated query now.

no

Do you think it would be better programming to do this last issue with a new query?

no!







r937.com | rudy.ca

 

[ChrisMacPherson]

Thanks, that sounds pretty certain!

Cheers for you help

Chris MacPherson