Group by wether or not the cell exists in another table as well

Question

My SQL syntax is MariaDB (MySQL)

I have a table with organisation spokepersons, and a table with VIP organizations, and a table with presentations. How do I group or sort by wether the spokeperson's organisation is VIP, so that VIP organisation spokepersons show up on top when retrieving all presentations?

Table presentations: int presentation_id, int person_id, varchar title, date date

Table persons: int person_id, varchar name, varchar function, varchar organisation

Table VIP_orgs: int org_id, varchar org_name

Query that doesn't work:

CREATE TABLE persons (
    person_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    organisation VARCHAR(64),
    PRIMARY KEY (person_id)
);

INSERT INTO `persons` (name, organisation) VALUES
    ("Guy Fieri",       "VIP-org"),
    ("Fiona",           "VIP inc."),
    ("Mr. Robot",       "Evil Corp"),
    ("Marcus Antonius", "Rome"),
    ("Cicero",          "Rome"),
    ("Shrek",           "VIP inc.");
    
CREATE TABLE presentations (
    presentation_id INT AUTO_INCREMENT,
    person_id INT,
    PRIMARY KEY (presentation_id)
);

INSERT INTO `presentations` (person_id) VALUES
    (1),(1),(1),(1), -- guy fieri has 4
    (2),
    (3),(3),(3),(3),(3),
    (4),(4),(4),(4),
    (5),(5),(5),
    (6),(6),(6),(6);

CREATE TABLE VIP_orgs (
    org_id INT AUTO_INCREMENT,
    org_name VARCHAR(64),
    PRIMARY KEY (org_id)
);

INSERT INTO `VIP_orgs` (org_name) VALUES 
    ("VIP-org"),
    ("VIP inc.");

SELECT organisation, COUNT(*) AS count
FROM `presentations`
JOIN `persons` ON `presentations`.person_id = `persons`.person_id
GROUP BY (SELECT org_name FROM `VIP_orgs` WHERE `VIP_orgs`.org_name = organisation), organisation
ORDER BY count DESC;

What I expect it to do:

return a table org_name, (total combined number of presentations by each spokeperson of that org)

Sorted by count of presentations, grouped by organisation, VIP organisations grouped on top. The VIP and non-VIP parts should be sorted by count independently. The returned table should thus look something like this:

name      count
VIP inc.  5
VIP-org   4
Rome      7
Evil Corp 5

The query works 50%: it counts all presentations and sorts it, but it doesn't seem to group by VIP organizations. In actuality the returned table looks like this:

name      count
Rome      7
VIP inc.  5
Evil Corp 5
VIP-org   4

user_5413565 · Answer

The schema doesn't look right. I would suggest creating an organisations table with a vip BOOLEAN column and add foreign key in persons table. Make the following changes in the schema:

CREATE TABLE `organisations` (
    organisation_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    vip BOOLEAN,
    PRIMARY KEY (organisation_id)
);

INSERT INTO `organisations` (name, vip) VALUES
    ("VIP-org", True),
    ("VIP inc.", True),
    ("Evil Corp", False),
    ("Rome", False);

CREATE TABLE persons (
    person_id INT AUTO_INCREMENT,
    name VARCHAR(64),
    organisation_id INT,
    PRIMARY KEY (person_id),
    FOREIGN KEY (organisation_id) REFERENCES `organisations`(organisation_id) 
);

INSERT INTO `persons` (name, organisation_id) VALUES
    ("Guy Fieri", 1),
    ("Fiona", 2),
    ("Mr. Robot", 3),
    ("Marcus Antonius", 4),
    ("Cicero", 4),
    ("Shrek", 2);

Now the query would look something like this:

SELECT `organisations`.name as organisation, COUNT(*) AS count
FROM `presentations`
JOIN `persons` ON `presentations`.person_id = `persons`.person_id
JOIN `organisations` ON `organisations`.organisation_id = `persons`.organisation_id
GROUP BY `organisations`.organisation_id
ORDER BY `organisations`.vip DESC, count DESC;

Output:

+--------------+------------+
| organisation |      count |
+--------------+------------+
| VIP inc.     |          5 |
| VIP-org      |          4 |
| Rome         |          7 |
| Evil Corp    |          5 |
+--------------+------------+

You can see the result here: db <> fiddle

user_14018331 · Answer

Instead of grouping by, I needed to sort. DOh!

Edit: this doesn't quite work. It does not sort by count. If I put the ORDER BY count clause first, it puts all vip orgs on the bottom.

Edit 2: using EXISTS, it seems to work

SELECT organisation, COUNT(*) AS count
FROM `presentations`
JOIN `persons` ON `presentations`.person_id = `persons`.person_id
GROUP BY organisation
ORDER BY EXISTS (SELECT org_name FROM `VIP_orgs` WHERE `VIP_orgs`.org_name = organisation) DESC, count DESC;

Group by wether or not the cell exists in another table as well

2 Answers