Multiple sum/counts across multiple tables in PostgreSQL

Question

I've searched through several suggestions on this site and haven't quite been able to get what I'm after. I suspect there's just a syntax/punctuation issue that I'm just missing.

I work on a database using phpPgAdmin that tracks lots of information related to a population of baboons being studied. I'm trying to make a query to identify, for each individual baboon, how many tissue samples of different types we have collected for them and how many DNA samples we have of different types for each of them There are three tables that are pertinent to my problem:

Table: "biograph" has basic info about all the animals in the group, though the name is all I care about here.

name | birth
-----+-----------
A21  | 1968-07-01
AAR  | 2002-03-30
ABB  | 1998-09-10
ABD  | 2005-03-15
ABE  | 1986-01-01

Table: "babtissue" tracks information, including the below three columns, about different tissues that have been collected over the years. Some lines in this table represent tissue samples that we no longer have, but are still referred to elsewhere in the database, so the "avail" column helps us screen for samples that we still have around.

name | sample_type | avail
-----+-------------+------
A21  | BLOOD       | Y
A21  | BLOOD       | Y
A21  | TISSUE      | N
ABB  | BLOOD       | Y
ABB  | TISSUE      | Y

Table: "dna" is similar to babtissue.

name | sample_type | avail
-----+-------------+------
ABB  | GDNA        | N
ABB  | WGA         | Y
ACC  | WGA         | N
ALE  | GDNA        | Y
ALE  | GDNA        | Y

Altogether, I'm trying to write a query that will return every name from biograph and tells me in one column how many 'BLOOD', 'TISSUE', 'GDNA', and 'WGA' samples I have for each individual. Something like...

name | bloodsamps | tissuesamps | gdnas | wgas | avail
-----+------------+-------------+-------+------+------
A21  | 2          | 0           | 0     | 0    | ?
AAR  | 0          | 0           | 0     | 0    | ?
ABB  | 1          | 1           | 0     | 1    | ?
ACC  | 0          | 0           | 0     | 0    | ?
ALE  | 0          | 0           | 2     | 0    | ?

(Apologies for the weird formatting above, I'm not very familiar with writing this way)

The latest version of the query that I've tried:

select b.name,  
sum(case when t.sample_type='BLOOD' and t.avail='Y' then 1 else 0 end) as bloodsamps,   
sum(case when t.sample_type='TISSUE' and t.avail='Y' then 1 else 0 end) as tissuesamps,   
sum(case when d.sample_type='GDNA' and d.avail='Y' then 1 else 0 end) as gdnas,  
sum(case when d.sample_type='WGA' and d.avail='Y' then 1 else 0 end) as wgas  
from biograph b  
left join babtissue t on b.name=t.name  
left join dna d on b.name=d.name  
where b.name is not NULL  
group by b.name  
order by b.name

I don't receive any errors when doing it this way, but I know the numbers it gives me are wrong--too high. I figure this has something to do with my use of more than one join, and that something about my join syntax needs to change.

Any ideas?

I sorted out your formatting but I wasn't sure what you wanted in the avail column so I left ? there. — mu is too short
– mu is too short, Commented Jul 27, 2011 at 19:59

Tom H · Accepted Answer · 2011-07-29 13:55:42Z

5

The numbers are too high because you're joining to babtissue and then also to dna, which is going to cause duplicates.

You can try to break it up. I don't know if this syntax will work for your database, but I believe that it follows ANSI standards, so give it a shot...

SELECT
    SQ.name,
    SUM(CASE WHEN T.sample_type = 'BLOOD' AND T.avail = 'Y' THEN 1 ELSE 0 END) AS bloodsamps,
    SUM(CASE WHEN T.sample_type = 'TISSUE' AND T.avail = 'Y' THEN 1 ELSE 0 END) AS tissuesamps,
    SQ.gdnas,
    SQ.wgas
FROM
    (
    SELECT
        B.name,
        SUM(CASE WHEN D.sample_type = 'GDNA' AND T.avail = 'Y' THEN 1 ELSE 0 END) AS gdnas,
        SUM(CASE WHEN D.sample_type = 'WGA' AND T.avail = 'Y' THEN 1 ELSE 0 END) AS wgas
    FROM
        biograph B
    LEFT JOIN dna D ON D.name = B.name
    GROUP BY
        B.name
    ) AS SQ
LEFT JOIN babtissue T on T.name = SQ.name
WHERE SQ.name is not NULL
GROUP BY SQ.name, SQ.gdnas, SQ.wgas
ORDER BY SQ.name

Can the name really be NULL?

edited Jul 29, 2011 at 13:55

answered Jul 27, 2011 at 19:50

Tom H

47.5k15 gold badges90 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mu is too short Over a year ago

PostgreSQL can handle all that.

Jake Over a year ago

Yeah, @Tom H., name can be null. There are several other columns in that table (I tried to omit irrelevant information), and it includes a line for every individual born in the population. Many of the animals die soon after birth, and therefore never are assigned a name. I gave that query a try, and it gave me a few minor errors that I was able to fix. The subquery needed a 'GROUP BY B.name', and the 'GROUP BY' at the end needed me to include the SQ.gdnas and SQ.wgas. But once I added those, it worked! Thanks!

mu is too short Over a year ago

@Jake: Sorry, I should have spotted the usual "ambiguous GROUP BY" problem but it was easy to fix. And you can accept the answer by clicking the checkmark beside Tom's answer.

Tom H Over a year ago

I've corrected the GROUP BY issues. Thanks for pointing that out.

gsiems · Accepted Answer · 2011-07-27 21:36:06Z

I don't know about the "avail" column, but this should give you the other columns you're looking for:

SELECT  b.name,
        COALESCE (t.bloodsamps,  0) AS bloodsamps,
        COALESCE (t.tissuesamps, 0) AS tissuesamps
        COALESCE (d.gdnas, 0) AS gdnas 
        COALESCE (d.wgas,  0) AS wgas
    FROM biograph b
    LEFT JOIN (
        SELECT  name,
                SUM(CASE WHEN sample_type = 'BLOOD'  THEN 1 ELSE 0 END) AS bloodsamps,
                SUM(CASE WHEN sample_type = 'TISSUE' THEN 1 ELSE 0 END) AS tissuesamps
            FROM babtissue
            WHERE avail = 'Y'
            GROUP BY name
        ) t
        ON (t.name = b.name)
    LEFT JOIN (
        SELECT  name,
                SUM(CASE WHEN sample_type = 'GDNA' THEN 1 ELSE 0 END) AS gdnas,
                SUM(CASE WHEN sample_type = 'WGA'  THEN 1 ELSE 0 END) AS wgas
            FROM dna
            WHERE avail = 'Y'
            GROUP BY name
        ) d
        ON (d.name = b.name)
;

Collectives™ on Stack Overflow

Multiple sum/counts across multiple tables in PostgreSQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related