SQL: Multiple select statements in one query

Question

I want to select information from three SQL tables within one query.

An example could be the following setup.

tblFriends

   id | idmother | dayBirth
  --------------------------
    1 |        1 | 09/09/21 
    2 |        2 | 09/09/21 
    3 |        3 | 11/09/21 
    4 |        3 | 11/09/21 
    5 |        4 | 07/09/21
  ... |      ... |      ...

tblMothers

   id |     name 
  --------------- 
    1 |    Alice
    2 | Samantha
    3 | Veronica 
    4 |    Maria
  ... |      ...

tblIsAssignedParty

   idMother | codeParty | price
  ------------------------------
          1 |       231 |    15
          2 |       645 |    28
          3 |       164 |    33
        ... |       ... |   ...

I want to have a query that gives me the following:

  dayBirth |   weekDay | totalFriendsForParty | totalFriendsForPartyPercent | totalFriendsNoParty | totalFriendsNoPartyPercent 
  -----------------------------------------------------------------------------------------------------------------------------
  07/09/21 |   Tuesday |                    0 |                           0 |                   1 |                       0.??    
  09/09/21 |  Thursday |                    2 |                        0.?? |                   0 |                          0  
  11/09/21 |  Saturday |                    2 |                        0.?? |                   0 |                          0

Note:

dayBirth = simply the day of birth; I need the friends grouped by this date
weekDay = dayBirth name
totalFriendsForParty = friends who will be attending the party; we know if the mother has a party assigned
totalFriendsForPartyPercent = Percentatge of friends, of the total number of friends who will attend the parties
totalFriendsNoParty = friends who will not attend the party; we know if the mother does not have a party assigned
totalFriendsNoPartyPercent = Percentatge of friends, of the total number of friends who will not attend the parties

I need the number of friends based on whether their mothers are at a party or not. I tried to multiple select statements in Single query but the following code didn't work:

SELECT 
(SELECT distinct dayBirth, TO_CHAR(dayBirth, 'DAY') from tblFriends) as firstSecondColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as thirdColumn,

(SELECT TRUNC(count(*) / count(thirdColumn.id) , 2) from tblFriends) as quarterColumn,

(SELECT dayBirth, count(*) from tblFriends
where idMother IN (
SELECT f.idMother 
from tblFriends f
left join tblIsAssignedParty iap
on f.idMother = iap.idMother 
where iap.codeParty is not null)
group by dayBirth) as fifthColumn,

(SELECT TRUNC(count(*) / count(fifthColumn.id) , 2) from tblFriends) as  sixthColumn,

order by dayBirth

Any advice on this one? I try to learn, I do what I can :-(

Edit: I can't add inserts because it's a file upload, but I can add an approximation of table creation.

Create tables:

  CREATE TABLE tblFriends
   (    
    id NUMBER(*,0),
    idMother CHAR(10 CHAR),  
     CONSTRAINT PK_FRIEND PRIMARY KEY (id, idMother), 
     CONSTRAINT FK_IDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    dayBirth DATE CONSTRAINT NN_DAY NOT NULL
   )


  CREATE TABLE tblMothers
   (    
    id CHAR(10 CHAR) CONSTRAINT PK_MOTHER PRIMARY KEY (id),   
    name VARCHAR2(20 CHAR) CONSTRAINT NN_MNAME NOT NULL
   )


  CREATE TABLE tblIsAssignedParty
   (    
    idMother CHAR(10 CHAR), 
    codeParty CHAR(10 CHAR),
    CONSTRAINT PK_ASSIGNED PRIMARY KEY (idMother, codeParty),  
    CONSTRAINT FK_ASSIGNEDMOTHER FOREIGN KEY (idMother)
      REFERENCES tblMothers (id),
    CONSTRAINT FK_ASSIGNEDPARTY FOREIGN KEY (codeParty)
      REFERENCES tblParties (codeParty),
    price DECIMAL(10,2)
   )

Please edit your question to include a minimal reproducible example with: the CREATE TABLE statements for your tables; the INSERT statements for your sample data that is sufficient to produce the complete output for your expected data; and an English (not code) description of what the columns mean and how to join them together to get the expected output. You have the columns codeparty, price and name that seem to be irrelevant to the question. It is unclear how you calculate who is a friend. Why is there a dateOfBirth column in the friends table and how does it relate to the date of a party? — MT0
– MT0, Commented Dec 3, 2021 at 10:49
I can't fully provide what you request, the tables have a lot more columns than that. I have better explained the result and attached the create to see how these tables are related. — Kuru
– Kuru, Commented Dec 3, 2021 at 11:18

MT0 · Accepted Answer · 2021-12-03 12:01:31Z

You appear to want to LEFT JOIN the firends and party tables and then use conditional aggregation:

SELECT dayBirth,
       TO_CHAR(dayBirth, 'FMDAY', 'NLS_DATE_LANGUAGE=English') AS day,
       COUNT(p.idmother)
         AS totalFriendsForParty,
       COUNT(p.idmother) / COUNT(*) * 100
         AS totalFriendsForPartyPercent,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) AS totalFriendsNoParty,
       COUNT(CASE WHEN p.idmother IS NULL THEN 1 END) / COUNT(*) * 100
         AS totalFriendsNoPartyPercent 
FROM   tblFriends f
       LEFT OUTER JOIN tblIsAssignedParty p
       ON (f.idmother = p.idmother)
GROUP BY dayBirth

Which, for the sample data:

CREATE TABLE tblFriends (id, idmother, dayBirth) AS
SELECT 1, 1, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 2, 2, DATE '2021-09-09' FROM DUAL UNION ALL
SELECT 3, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 4, 3, DATE '2021-09-11' FROM DUAL UNION ALL
SELECT 5, 4, DATE '2021-09-07' FROM DUAL;

CREATE TABLE tblIsAssignedParty (idMother, codeParty, price) AS
SELECT 1, 231, 15 FROM DUAL UNION ALL
SELECT 2, 645, 28 FROM DUAL UNION ALL
SELECT 3, 164, 33 FROM DUAL;

Outputs:

DAYBIRTH DAY TOTALFRIENDSFORPARTY TOTALFRIENDSFORPARTYPERCENT TOTALFRIENDSNOPARTY TOTALFRIENDSNOPARTYPERCENT

09-SEP-21 THURSDAY 2 100 0 0

11-SEP-21 SATURDAY 2 100 0 0

07-SEP-21 TUESDAY 0 0 1 100

db<>fiddle here

I have a doubt. What does "COUNT (CASE WHEN p.idmother IS NULL THEN 1 END)" do?
@Kuru Counts the rows where the LEFT OUTER JOIN does not match a friend to a party.
Oh... Okay, I underestand. I have had a very serious error when indicating the fields of the tblIsAssignedParty table. I have noticed when applying it to my BD and seeing that it was always 0. However, I hope I can adjust it based on what you have explained to me... although at the moment I am not sure what to change, I think a join is missing. Thanks for your help, I've been doing this for many hours and it's all dumb mistakes.

Collectives™ on Stack Overflow

SQL: Multiple select statements in one query

1 Answer 1

3 Comments

Hot Network Questions

DAYBIRTH	DAY	TOTALFRIENDSFORPARTY	TOTALFRIENDSFORPARTYPERCENT	TOTALFRIENDSNOPARTY	TOTALFRIENDSNOPARTYPERCENT
09-SEP-21	THURSDAY	2	100	0	0
11-SEP-21	SATURDAY	2	100	0	0
07-SEP-21	TUESDAY	0	0	1	100

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related