I’m trying to create a query that will display a sum total of “points” by “team”.
I made a server action query and connected it to a page with a responsive table
When I add SUM in the aggregate the responsive table shows just one line per team entry but no data. If I take the sum away it shows a record for each entry with the points filled in.
This is the first I have tried to build a table that summarizes data so I suspect I’m building it wrong?
Well having sum results in summing the records to a single row with the totals.
If you want both - the records and a totals - then you should just make two queries in the server action and return them both.
I want the sum of points grouped by team so the database would list each team with their total points summarized
Asked similar thing already:
In the Query Builder in the Columns tab add the team and points column, then select the SUM for the points in the Aggregate column. It will automatically group by team then.
You know I saw this before but I wasn’t counting the same field as in the example. Anyway I tried changing it to a count of the teams and added an alias like in the example. That worked so I went back in and did my sum again and now it works. I wonder if adding an alias was the key?
Anyway I got it working now, It just seemed too simple.
Now I need to figure out how to sort the data by the sum of the points. sorting by the points field didn’t do it. Thanks for your input
That’s what I was doing but it didn’t show up in the table generator. I’m not sure if the key was adding an alias or just re-running the generator, either way it’s working now. Thanks for your time.
I’m having trouble sorting the data by the sum of the points. I tried adding a repeat and then outputting the repeat rather than the query but I get the same data. What and I missing?
I’m not figuring this out? I want to summarize the total then sort by the total.
Could you either send a live link to your page, or possibly post some screenshots or a code snippet or video of the process so I can possibly try and assist?
Thanks for the reply
I’m taking the results and pulling our the driver and the points. I can get the points to summarize the points by driver but I don’t know how to sort them by the sum
here is a link to the page
http://racesaverregistration.com/Standings.php?sort=Pts&dir=asc
Do you need to do that particularly at the server side level, can you not do the sorting on the client side rather.
When you bind your dynamic attribute to your page, then slick the magic want data formatter for your alias column of Pts and set the sort to ASC or DESC in there?
Ok with the link I can see a little better.
So first thing is on your displayed data I can see the Driver Rod Ort has a blank in the points column, you need to set default to the binding if you are going to be using paging or sorting.
If you are getting this data like this
<td dmx-text="Pts"></td>
Change it to
<td dmx-text="Pts.default(0)"></td>
It would be easiest if you could upload the file here as a zip so i can look at it properly though and advise, or just add the code into a code block here. The reason I ask is because the page you are showing also has a 404 error for role_checker.php so I am not sure if that is also contributing to breaking stuff too.
Standings.zip (1.3 KB)
Role checker is in the nav bar include
Ok so lets try a few things just to see how we go, this line seems strange as you are only getting the first result from the array.
<dmx-serverconnect id="ActivePoints" url="dmxConnect/api/Data/Standings/Points.php" dmx-param:sort="ActivePoints.data.StandingsQuery[0].Pts" dmx-param:dir="DESC"></dmx-serverconnect>
Change to
<dmx-serverconnect id="ActivePoints" url="dmxConnect/api/Data/Standings/Points.php" dmx-param:sort="ActivePoints.data.StandingsQuery.Pts" dmx-param:dir="DESC"></dmx-serverconnect>
Let me know when changed and uploaded to check it please.
seems the same
Try remove all sorting from the server connect query please.
Then try this, I am not sure if it will work but I have got around something similar with this before.
<td dmx-text="Pts.default(0).sort()"></td>
EDIT - we need to sort the array of data before we repeat it, it is difficult to diagnose without the queries etc. I am going to try make something similar to your use case just to test quickly.
Ok I think I have made something similar and mine works with this please go to your query and remove any sort columns you have set and then replace your tables tbody with the below.
<tbody is="dmx-repeat" dmx-generator="bs4table" dmx-bind:repeat="ActivePoints.data.StandingsQuery.sort('Pts')" id="tableRepeat2">
<tr>
<td dmx-text="Driver"></td>
<td dmx-text="Pts.default(0).toNumber()"></td>
</tr>
</tbody>
EDIT: I added toNumber() to the td as you may need that.
This seems to be working, but it looks like its sorting as text not numbers. I really appreciate your time. I gotta get out of here in a few minutes but I'll work on it Monday. Thanks
Cool, you might need to add toNumber() to td or more likely the sort in the array
<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).toNumber()"></td>
</tr>
</tbody>
OR
<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>