CodeIgniter Forums
Calculate show points in SQL - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Calculate show points in SQL (/showthread.php?tid=91375)

Calculate show points in SQL - HarmW94 - 07-31-2024

Hi,

This query works now, but it needs to be more specific for the customer. I also looked to see if I can adjust the data in the (PHP) array, but I prefer to pull it out of the query so that sorting continues to work properly. I've been stuck on this part for a while now. SQL statements are not my specialty.

Now it shows the total points well, but what I want to achieve is the following:

    It must count the points of the national show (type 1) first, since an animal participates in a national show a maximum of 1 time per year.
    Then it must select the 3 highest points and then stop counting (LIMIT).

So when an animal has not yet participated in a national show, it takes the 3 highest scores and if one has participated, it still takes the 3 highest scores, but the national one must be above that.

(show_participants.participant_category = "1" is now static, but this is for testing. There are 10 categories)

Hope it's clear, thanks a lot in advance.

Code:
SELECT Sum(participant_match_points) AS participant_total_points,
      shows.show_type,
      show_types.type_name,
      animals.born,
      animals.registrationnumber,
      owners.ownernumber
FROM  show_participants
      INNER JOIN animals
              ON animals.registrationnumber =
                  show_participants.participant_animal
      INNER JOIN show_categories
              ON show_participants.participant_category =
                  show_categories.category_id
      INNER JOIN owners
              ON animals.registrationnumber = owners.registrationnumber
      INNER JOIN shows
              ON show_participants.participant_show = shows.show_id
      INNER JOIN show_types
              ON show_types.type_id = shows.show_type
WHERE  show_participants.participant_category = "1"
GROUP  BY show_participants.participant_animal
ORDER  BY participant_total_points DESC

[Image: 2c5b338acaa556e4296dae4f6a80f0131a2bfca7.png]

RE: Calculate show points in SQL - JustJohnQ - 08-03-2024

How about:

Code:
ORDER  BY participant_total_points DESC, shows.show_type ASC


??