Sum value not showing in query

Server Side Components
#21

So close. Gonna try more next week. Thanks again

#22

Sorry guys, come into this a bit late. Can i see what the table structure is and can you tell in exactly what you want the query to do. This sounds like it is getting more complicated than it needs to.

#23

Thanks @Hyperbytes . It should be simple. I want to summarize the data and then sort it by the totals however doing it server side would not work for me. @psweb has been helping me try to make it work.

#24

Did you try the final code i sent to see if it would do what is required.

<tbody is="dmx-repeat" dmx-generator="bs4table" dmx-bind:repeat="ActivePoints.data.StandingsQuery.sort(Pts.toNumber())" id="tableRepeat2">
  <tr>
    <td dmx-text="Driver"></td>
    <td dmx-text="Pts.default(0)"></td>
  </tr>
</tbody>
#25

Hey Brian, I assume @rokit has a database with something like an ID, DriverName, Points and each time a driver earns points from a race then they get another entry into the table with another 3 points or 5 points or whatever.
Therefore there might de 20 entries for a single driver.

He wants to do a query with a single drivers name and the sum of all their points which he has done as an alias of the column.

That is working, all he then wants to do is sort the drivers names dependant on how many points they have.
The alias column will not do a sort on it as its sum, so I said he will have to do the server side query, then sort the data client side.

The only easier way of doing this i assume would be to use a view in mysql which could already sum and sort before the query is even made, but the client side sorting should also work fairly simply.

#26

need to know structure. it really should be two tables, a driver table and a pionts table but i suspect it is flat file.

#27

Exactly. I have table “results” which is the individual results from each race. I just want to query it for the current season sumarize the points and sort them high to low.

I’m using the same file under the page for drivers simply filtering for the finish “1” and grouping it by year to easily give me their wins each year. Here is an example

http://racesaverregistration.com/TeamHomePage.php?id=20

I just realized I should look at this query because somehow it is sorting by the year correctly.

By the way you guys are awesome helping out all hours of the week. thanks

#28

So you use 1 table or 2?

My suggestion would be:

tableDriver
DriverID
Drivername
Other info needed

TableResults
ResultID (primary key)
PointsDate
DriverID
Points

If wanted you could also store racing info

tableRace
RaceID
RaceDate
RaceName

and then amend the results table to

TableResults
ResultID
DriverID
RaceID
Points

#29

The Results table Includes

ResultID (primary Key)
RaceID
TeamID
Finish
Points
Track
Pick 10 (used to filter their best 10 finishes
other misc data

I can add other data (such as the driver name using teamID)

Even with the flat file (no links) If I just use TeamID and Points(sum) I can’t get them to sort by the Points (sum) (yes points is an Int field)

I would probably need to sort by the alias I use for the sum “Pts” but I can’t figure that out.

#30

The query should be like this

SELECT TeamID, SUM(Points) AS totalpoints from raceinfo GROUP BY TeamID ORDER BY totalpoints Desc

#31

Apologies, i see the problem, the alias does not appear in the Sorting tab, that is a problem! Not sure yet how to get around rhis

#32

@patrick, how do we sort by the aggregated column as it does not appear int the sorting tab

#33

Yup that is exactly the issue, which is why I have suggested the sort gets done client side rather. I think he will try it on Monday and then we can see if it works as it should.

#34

That really should not be necessary, this if effectively a bug in Wappler albeit a bug by omission. Lets see what partrick has to say about this, he suggested the group by but did not clarify the sort.

#35

In the past I used and access database so I would create the query there. Wappler works incredibly well with managing this data using MySQL so I’m just trying to learn the best way. I sincerely appreciate your feedback. The ability to sort by the alias sever side would be great but that easy for me to say, I have no idea what it takes to add that feature. For now I will try it again with the client side sort. No rush. Points for 2018 are over so I have months to get this done!

#36

The Query Builder currently doesn’t have the option to select an alias for sorting.

If you want to use it then you have to edit the JSON of the action file directly.

"orders": [
  {
    "column": "totalpoints",
    "direction": "DESC"
  }
],

Will see if we can implement it in the Query Builder UI.

1 Like
#37

Client side sorting will do the job but it shouldn’t be necessary and certainly is not the most efficient way of doing things in my opinion

#38

I often have to modify fields for sorting purposes - eg stripping off leading characters using the TRIM function. Could I edit the JSON file as in your example to achieve this?

I thought it would be necessary to use a trigger or calculated field in the MySQL table, but I would prefer to do it within Wappler if possible - even if it means editing the JSON files.

#39

I’ finally revisiting this and I still haven’t figured out how to sort by an alias like sum or count

Here is a page I made. I would like to sort by PointsTot

Furthermore rathe than trying to do it with code would it be possible to include the “alias” as a sorting option inn the query?
http://racesaverregistration.com/PointStanings.php

#40

Hi @patrick,
I have the same problem as @rokit do you have an update on Sorting on Alias?