Thread: SQL help?

Trying to do something very easy, but can't quite figure out the logic.
Typing out the email, and see if I can't figure it out just typing it
out.

Have two tables:

petitions
id = (int)primary key
name = text description

and the following

signed_petitions
signed_id = references petitions.id (Foreign key)
joined = boolean

my goal is to list those petitions that have the most signatures, so I
have to get a count of joined=true, and list the names.

What confuses me on a fundamental level, is the usage of the Foreign
key, reference. But, here is my best attempt.

Ok, this is a lot harder than I thought, because the boolean is not
actually a number. So I guess I will have to use a rank?

SELECT 
    petitions.id
    petitions.name
    RANK() OVER (PARTITION BY signed_petitions.joined) AS total_signed
WHERE 
    signed_petitions.joined = "TRUE"
ORDER BY total_signed DESC
LIMIT 10



I am not sure how the rank() over (partition by... works), this is my
first attempt.

Would this give me the ten highest signed petitions?

A Foreign Key is a mechanism for the database to insure that all of the signed_id values in signed_petitions exist in
theid column of the petitions table. (I am ignoring NULL handling for the moment, but look that up later once you feel
thatyou understand foreign keys a little better).  The foreign key relationship also gives you the likely columns that
you'llwant to use to join the two tables.
 

Looking at your query below the first thing I notice is the lack of a FROM clause telling the query which tables you
arelooking at.  Next is that RANK() is likely not the best candidate for answering your question (not that it couldn't
beused, but that there is a shorter method for doing so).
 

I am going to assume that "petitions" is just the list of the different petitions.  For "signed_petitions", I am
unclearif this is just a subset of petitions that have at least one signature, or if the "signed_petitions" table shows
allof the people who have been shown the petitions and whether or not they have signed it.  I will base the rest of
thisanswer on the latter assumption - that "signed_petitions" includes the list of people who have signed each petition
becauseyou referenced wanting to know which petition has the most signatures.  
 

For the FROM clause, you told us the two tables and the foreign key relationship, so we can put that together as
follows(note, this is just the first conceptual step)
 

SELECT petition.name, signed_petitions.joined 
FROM petitions
INNER JOIN signed_petitions
ON petitions.id = signed_petitions.signed_id

This gives one row for every signature for every petition, and doesn't, yet, answer the full question. To get the
numberof signatures, the easiest function to use is COUNT().  Putting that in, we get this:
 

SELECT petitions.name,  count(signed_petitions.joined)
FROM petitions
INNER JOIN signed_petitions
ON petitions.id = signed_petitions.signed_id
GROUP BY name

This gives the count of records in signed_petitions for each petition, regardless of whether or not joined=true.  So we
canadd that WHERE clause back in.  NOTE, for a Boolean column, you don't actually need to put the equal sign, a very
usefulsyntax, but harder for novices to understand.  NOTE2: "true" should not be in double quotes, nor even single
quotes.

SELECT petitions.name,  count(signed_petitions.joined)
FROM petitions
INNER JOIN signed_petitions
ON petitions.id = signed_petitions.signed_id
WHERE signed_petitions.joined = true
GROUP BY name

This still ignores the case that some petitions may not have any records in signed_petitions.  We can account for that
witha LEFT JOIN instead of INNER JOIN.
 

SELECT petitions.name,  count(signed_petitions.joined)
FROM petitions
LEFT JOIN signed_petitions
ON petitions.id = signed_petitions.signed_id
WHERE signed_petitions.joined = true
GROUP BY name

And finally, what if we want to know the counts for how many have joined=true AND joined=false all in the same query?

SELECT petitions.name
,  count(signed_petitions.joined) FILTER (WHERE joined = true) as num_joined
,  count(signed_petitions.joined) FILTER (WHERE joined = false) as num_not_joined
FROM petitions
LEFT JOIN signed_petitions
ON petitions.id = signed_petitions.signed_id
GROUP BY name

I hope this, as a tutorial both answered your intended question and gave you, or other future readers, help in
conceptuallyputting these pieces together.
 

Dan 

-----Original Message-----
From: Dr. Drexl Spivey [mailto:drexl@little-beak.com] 
Sent: Wednesday, May 22, 2019 3:17 AM
To: pgsql-novice <pgsql-novice@lists.postgresql.org>
Subject: SQL help?

Trying to do something very easy, but can't quite figure out the logic.
Typing out the email, and see if I can't figure it out just typing it
out.

Have two tables:

petitions
id = (int)primary key
name = text description

and the following

signed_petitions
signed_id = references petitions.id (Foreign key)
joined = boolean

my goal is to list those petitions that have the most signatures, so I
have to get a count of joined=true, and list the names.

What confuses me on a fundamental level, is the usage of the Foreign
key, reference. But, here is my best attempt.

Ok, this is a lot harder than I thought, because the boolean is not
actually a number. So I guess I will have to use a rank?

SELECT 
    petitions.id
    petitions.name
    RANK() OVER (PARTITION BY signed_petitions.joined) AS total_signed
WHERE 
    signed_petitions.joined = "TRUE"
ORDER BY total_signed DESC
LIMIT 10



I am not sure how the rank() over (partition by... works), this is my
first attempt.

Would this give me the ten highest signed petitions?


E-MAIL CONFIDENTIALITY NOTICE: 
The contents of this e-mail message and any attachments are intended solely for the addressee(s) and may contain
confidentialand/or legally privileged information. If you are not the intended recipient of this message or if this
messagehas been addressed to you in error, please immediately alert the sender by reply e-mail and then delete this
messageand any attachments. If you are not the intended recipient, you are notified that any use, dissemination,
distribution,copying, or storage of this message or any attachment is strictly prohibited.