Different price for additional members of the same group-CodePudding

I have a simple table like this:

group | name | price
1     | john |
2     | mike |
3     | paul |
1     | sean |
4     | jack |
2     | brad |
5     | mick |
1     | bill |
4     | chad |

I have two different price values where 100EUR is for a first member of a group and 50EUR is for all additional members of that same group.

Detailed explanation. If a group has only one member, that member gets a price of 100EUR. If a group has multiple members, the first member gets a price of 100EUR, and all additional members of that same group get a price of 50EUR. There can be unlimited number of groups that will be added additionally.

The result should be like this:

group | name | price
1     | john | 100
2     | mike | 100
3     | paul | 100
1     | sean | 50
4     | jack | 100
2     | brad | 50
5     | mick | 100
1     | bill | 50
4     | chad | 50

I'd need a query which would be able to INSERT/UPDATE all missing price fields whenever I manually run it.

Thank you in advance for looking into that matter.

CodePudding user response：

A partial answer: you can GROUP BY your "group" field and tack on a HAVING COUNT(group) > 1 to determine if that group has more than 1 member.

That is, to see all groups with more than one member it would look like:

SELECT
group
FROM table
GROUP BY group
HAVING COUNT(group) > 1

That will just tell you which groups have multiple members. Without another way to ensure ordering you cannot tell which member is "first" in their group and thus should be priced at 100 and all others priced at 50.

CodePudding user response：

The following queries are not tested and might contain syntax errors. But they are good enough to understand the principle. There are many possible ways to achieve your result.

Here is my take: I would make use of one query to UPDATE the price on every row and set it to 50 whether it is the first group member or not. >table_name<, of course, needs to be changed to the name of your mentioned table.

UPDATE >table_name<
SET price = 50;

Then I would take care of each individual group and the respective first member by running the following query. Adapt the query to each group by changing the >groupId<.

UPDATE >table_name<
SET price = 100
WHERE id = (
    SELECT id
    FROM >table_name< 
    WHERE group = >groupId< 
    ORDER BY id
    LIMIT 1
);

Take a look a the nested query: It queries the table for all members of only one group, orders them in ascending order and only returns an id per member. By applying LIMIT to the query, the result will just be the first group member's id. The resulting id can then be used in the other query to update the price and set it to 100.

But be careful: If you insert/delete (new) members with an id that is not just counting up, this query might select a "new first member".