Is there a way to keep the detailed rows when grouping and doing a sum?
Tried reading the documentation on Sum.
When I group on 1 field and do the sum of the amount field we get the right sum, but we only get the summed row, we lose the rows showing the details. I think I know the SQL to keep this, but trying to utilize the table functions.
See attachments for different scenarios. We want to have the sum for all the rows per customer, and we use the sum function for the FLOAT field, but we of course only get 1 row, we want to keep the detailed rows as reference.
When we group by all fields, the sum goes to 0.
SCENARIO 1 (result in attachment TABLESUM_0.jpg)
SELECT ClientResources.`ValNdx`,
ClientResources.`ResourceSSN`, ClientResources.`ResourceType`, ClientResources.`ResourceName`, ClientResources.`ResourceBal` FROM ClientResources
GROUP BY ClientResources.`ResourceSSN`,ClientResources.`ResourceName`
SCENARIO 2 (result in attachment TABLESUM_1.jpg)
SELECT ClientResources.`ValNdx`, ClientResources.`ResourceSSN`, ClientResources.`ResourceType`, ClientResources.`ResourceName`, ClientResources.`ResourceBal` FROM ClientResources
GROUP BY ClientResources.`ResourceSSN`
There is a column being hidden which is an auto-increment column, not necessary except we need it for editing identification not display. We may not edit anyhow, but turned it off from display, not sure it has anything to do with this.
Just an observation:
Looks like the FLOAT is showing commas and periods intermixed. Hard to see, but looks like 1,000.50 shows up as 1.000,50
I actually took a magnifying glass to the monitor and saw that because it was small from way back, but it did appear off, just was not sure.
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
You can change number format with option Number format in main settings of plugin.
Can you give me a hint or clarify where this stored VIEW is? I created VIEWS on MYSQL so that is no problem, but when I try to run the VIEW from WPDATA, it says no data to load from table, but it is the VIEW I am accessing which in turn pulls from the table in MYSQL.
I apologize for my ignorance, I have the objects, but not sure how to set it up in WPDATA. This is for the GROUP BY issue correct? Not the comma/period issue.
SELECT * FROM VIEW.CleintRES
ClientRES is the view on the MYSQL.
I try ROLLUP, but WITH is deprecated so I use it around the GROUP BY with all the fields, used SUM around the ResouceBal field, and it shows the records but no Grand Total, only when it does, it loses the records as mentioned.
This used to be easy years ago, honestly have not needed a grand total for that many years so I am feeling pretty stupid. I see all the examples, but I cannot get it to work here.
Gave it a thought, not sure why, just a hunch can't hurt kind a thing.
I did the actual MYSQL table (not VIEW of said table) and did all what I did before and took off the SERVER SIDE PROCESSING option. And it works now, I get the SUM totally, no pun intended. I decided to take what you told me about that option but use it on the table not the view. Just one hitch, the commas the the decimal points are switched as shown in the attachment Not sure what to think, perhaps it is another plugin. At least we get the SUM with the recs showing! Just need to stop the comma/decimal switching.
ValNdx
ResourceSSN
ResourceType
ResourceName
ResourceBal
1
900-00-9999
Bank01
TD Bank
300,44
2
900-00-9999
Bank02
AFCU
0,00
902
900-00-9999
Bank03
Chase Bank
3.123,44
903
900-00-9999
Bank04
Wells Fargo
1.200,00
∑ = 4.623,88
UPDATE:
Thought I would try to create the WPDATA table from scratch, it looked like it might work as ClientRES showed up to pick. However it cannot find data. I took off the GROUP BY even though that should not matter, and it did not, still no data. Like it sees the VIEW, but cannot read off it properly.
Something on MYSQL I did not set??
info No data fetched! If you are trying to save table for the first time, please enter some date before saving so table could be set accurately. You can remove it later if you need empty table to start with.
Rendered query: SELECT ClientRES.`ResourceSSN`, ClientRES.`ResourceType`, ClientRES.`ResourceName`, ClientRES.`sum(ClientResources` FROM ClientRES GROUP BY ClientRES.`ResourceSSN`, ClientRES.`ResourceType`, ClientRES.`ResourceName` LIMIT 10 MySQL said: Unknown column 'ClientRES.sum(ClientResources' in 'field list'
EARLIER REPLY:
Ok, that sounds totally right, however it goes back to what I recall it saying before I put in VIEW, thinking it needed VIEW, and that it cannot find it, looking for a table it is. However it is stored as a VIEW. I'll keep looking and playing, but it looks pretty much a problem.
"Rendered query: SELECT * FROM `ClientRes` GROUP BY `ResourceSSN`, `ResourceType`, `ResourceName` LIMIT 10 MySQL said: Table 'srcaid.ClientRes' doesn't exist"
UPDATE: I'm losing it, sorry, but I keep checking things and found you guys have two options for number formatting in the Main OPTIONS. I chose the one with comma and decimal as we are used to seeing and that works now.
So the main issue is why when we edit a floating number, the Grand Total wigs out and only then sees the first row?
What to keep this separate from the UPDATES as it has added bad news, but may provide clues.
I tried editing the number field, it is set as Floating 2 decimal spaces.
Odd stuff, here are my test cases.
4.44 entered turns out as 444,00 on WPDATA and 444.00 in MYSQL dbase
4,44 entered gives 4,44 in WPDATA and 4.44 in MYSQL
13 entered gives 13,00 in WPDATA and 13.00 in MYSQL
Entering a comma translates as a comma in WPDATA, but acts as a decimal in MYSQL
Entering a decimal seems to add two places (x 100)
Entering an integer assumes a decimal so it throws the same as the previous case (decimal).
The really bad news is the the GRAND TOTAL never comes back, it seems to isolate on the first row value no matter what.
What I noticed though is when the table is refreshed, it quickly shows the proper GRAND TOTAL, but it resets and comes back to the same thing, just the first row used. So it tries to do it ok, but seems to refresh like an AJAX move and goes back to wrong.
We are located in Serbia and our working time is from 10:00 to 17:00 CET. business days.
From error that you get it looksile that you made view in database that is not connected in wpdatatables.
Point of the view is to make query that you need and then use is wpdatatables like SELECT * FROM view_name. If you adding additional sql statements with the view there is a chance that data will not be shown properly because wpDataTables server has problems with parsing of the query and building new queries dynamically.
Please note some this when working with the server-side processing feature:
Please do not use “LIMIT” in the SELECT statement. wpDataTables adds it automatically and it will be overridden.
Please do not use “ORDER BY” in the SELECT statement. wpDataTables has its own sorting engine so it makes no sense to use MySQL’s sorting, since it will be overridden. Also server-side processing feature adds this part of statement automatically when users triggers the sorting on the front-end, and having it in initial statement may cause the table to crash.
About the Number format, yes there is some cases where you have to set proper format so layout of the numbers will shown like you need.
Ok, thanks Milan, I totally understand. I think I have too many comments and need to summarize to make sure all is covered.
(1) The format issue I found as my mistake and did not see the format options drop down, commas and decimals are cool now.
(2) I manage to get the detail records and Grand Total ok, ONLY if I do not turn on EDITING and I do not have to use a VIEW. Once I turn on EDITING and make sure SERVER SIDE PROCESSING is OFF, then all is ok. What happens is if EDITING is on, the Grand Total only sees the first record's number. You can see the Grand Total for almost a second, then the page refreshes with the wrong results.
So if I need to edit the records it has to be done via our forms going into MYSQL. I do not have a working EDITING scenario with WPDATA when I Group By. All other tables we use in WPDATA edit fine.
(3) Problem having VIEW linked to WPDATA. I created the simple SELECT* from ClientRES which is our view and WPDATA cannot find the table ClientRES so is there something I need to do to have VIEWS work and not just tables?? This was part of my comments earlier.
Yes we have done many tables, I have for two clients who I purchased WPDATA Pro for. The connection is a separate one we have been 100% successful with and I share your confusion why the VIEW is not somehow being recognized on WPDATA.
Yes, we would like to keep editing for this particular kind of table, but since the editing is not working or say keeping the Grand Total properly after an edit is made, we keep it as informational and will have to use our form plugin to do that.
I guess the theme did fix the drop down display issue but for one table not the other so I will check out the much larger table and compare it on the config and see what is different. Just found that out now when I re-tested the tables.
So that leaves the summation getting changed when editing a table with the group by and sum. The other issue above I am thinking it theme related and something I did or did not to the larger table might be fixed comparing the smaller one that is now working.
Yes we can see that table with id 6 and the issue with grand total is because serverside processing is turn on. Like we explain in previous posts this kind of issues can be fixed by making the view in database. Because we can't access to the database to check this. My suggestion is to turn off serverside processing (if you don't need editing) or to do workaround with Forms to do the editing and the table to bring in the data that you mentioned in previous posts.
Is there a way to keep the detailed rows when grouping and doing a sum?
Tried reading the documentation on Sum.
When I group on 1 field and do the sum of the amount field we get the right sum, but we only get the summed row, we lose the rows showing the details. I think I know the SQL to keep this, but trying to utilize the table functions.
See attachments for different scenarios. We want to have the sum for all the rows per customer, and we use the sum function for the FLOAT field, but we of course only get 1 row, we want to keep the detailed rows as reference.
When we group by all fields, the sum goes to 0.
SCENARIO 1 (result in attachment TABLESUM_0.jpg)
SELECT ClientResources.`ValNdx`,
ClientResources.`ResourceSSN`,
ClientResources.`ResourceType`,
ClientResources.`ResourceName`,
ClientResources.`ResourceBal`
FROM ClientResources
GROUP BY ClientResources.`ResourceSSN`,ClientResources.`ResourceName`
SCENARIO 2 (result in attachment TABLESUM_1.jpg)
SELECT ClientResources.`ValNdx`,
ClientResources.`ResourceSSN`,
ClientResources.`ResourceType`,
ClientResources.`ResourceName`,
ClientResources.`ResourceBal`
FROM ClientResources
GROUP BY ClientResources.`ResourceSSN`
There is a column being hidden which is an auto-increment column, not necessary except we need it for editing identification not display. We may not edit anyhow, but turned it off from display, not sure it has anything to do with this.
Just an observation:
Looks like the FLOAT is showing commas and periods intermixed. Hard to see, but looks like 1,000.50 shows up as 1.000,50
I actually took a magnifying glass to the monitor and saw that because it was small from way back, but it did appear off, just was not sure.
Hi Alan,
Thank you for your purchase.
If your MySQL-query based wpDataTable doesn’t work correctly with server-side processing, probably this is happening because wpDataTables server has problems with parsing of the query and building new queries dynamically (happens rarely, but does sometimes). To avoid this please prepare a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTabled based on a simple query like “SELECT * FROM view1″.
You can change number format with option Number format in main settings of plugin.
Best regards.
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables
Can you give me a hint or clarify where this stored VIEW is? I created VIEWS on MYSQL so that is no problem, but when I try to run the VIEW from WPDATA, it says no data to load from table, but it is the VIEW I am accessing which in turn pulls from the table in MYSQL.
I apologize for my ignorance, I have the objects, but not sure how to set it up in WPDATA. This is for the GROUP BY issue correct? Not the comma/period issue.
SELECT *
FROM VIEW.CleintRES
ClientRES is the view on the MYSQL.
I try ROLLUP, but WITH is deprecated so I use it around the GROUP BY with all the fields, used SUM around the ResouceBal field, and it shows the records but no Grand Total, only when it does, it loses the records as mentioned.
This used to be easy years ago, honestly have not needed a grand total for that many years so I am feeling pretty stupid. I see all the examples, but I cannot get it to work here.
Hi Alen,
If you make view in database which name is ClientRES then you can use it in query like this:
SELECT * FROM CleintRES
You don't need to insert VIEW. in query.
Best regards.
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables
UPDATE #2:
Gave it a thought, not sure why, just a hunch can't hurt kind a thing.
I did the actual MYSQL table (not VIEW of said table) and did all what I did before and took off the SERVER SIDE PROCESSING option. And it works now, I get the SUM totally, no pun intended. I decided to take what you told me about that option but use it on the table not the view. Just one hitch, the commas the the decimal points are switched as shown in the attachment Not sure what to think, perhaps it is another plugin. At least we get the SUM with the recs showing! Just need to stop the comma/decimal switching.
UPDATE:
Thought I would try to create the WPDATA table from scratch, it looked like it might work as ClientRES showed up to pick. However it cannot find data. I took off the GROUP BY even though that should not matter, and it did not, still no data. Like it sees the VIEW, but cannot read off it properly.
Something on MYSQL I did not set??
info No data fetched!
If you are trying to save table for the first time, please enter some date before saving so table could be set accurately.
You can remove it later if you need empty table to start with.
Rendered query: SELECT ClientRES.`ResourceSSN`, ClientRES.`ResourceType`, ClientRES.`ResourceName`, ClientRES.`sum(ClientResources` FROM ClientRES GROUP BY ClientRES.`ResourceSSN`, ClientRES.`ResourceType`, ClientRES.`ResourceName` LIMIT 10
MySQL said: Unknown column 'ClientRES.sum(ClientResources' in 'field list'
EARLIER REPLY:
Ok, that sounds totally right, however it goes back to what I recall it saying before I put in VIEW, thinking it needed VIEW, and that it cannot find it, looking for a table it is. However it is stored as a VIEW. I'll keep looking and playing, but it looks pretty much a problem.
"Rendered query: SELECT * FROM `ClientRes` GROUP BY `ResourceSSN`, `ResourceType`, `ResourceName` LIMIT 10
MySQL said: Table 'srcaid.ClientRes' doesn't exist"
<span class="cm-keyword"><a href="https://east1-phpmyadmin.dreamhost.com/url.php?url=https://dev.mysql.com/doc/refman/5.5/en/select.html" target="mysql_doc" class="cm-sql-doc" rel="nofollow">SELECT</a></span> * <span class="cm-keyword">FROM</span> <span class="cm-variable-2">`ClientRES`</span><br>Profiling [Edit inline] [ Edit ] [ Explain SQL ] [ Create PHP code ] [ Refresh ]
UPDATE: I'm losing it, sorry, but I keep checking things and found you guys have two options for number formatting in the Main OPTIONS. I chose the one with comma and decimal as we are used to seeing and that works now.
So the main issue is why when we edit a floating number, the Grand Total wigs out and only then sees the first row?
-------------------------------------------------------------------------
What to keep this separate from the UPDATES as it has added bad news, but may provide clues.
I tried editing the number field, it is set as Floating 2 decimal spaces.
Odd stuff, here are my test cases.
4.44 entered turns out as 444,00 on WPDATA and 444.00 in MYSQL dbase
4,44 entered gives 4,44 in WPDATA and 4.44 in MYSQL
13 entered gives 13,00 in WPDATA and 13.00 in MYSQL
Entering a comma translates as a comma in WPDATA, but acts as a decimal in MYSQL
Entering a decimal seems to add two places (x 100)
Entering an integer assumes a decimal so it throws the same as the previous case (decimal).
The really bad news is the the GRAND TOTAL never comes back, it seems to isolate on the first row value no matter what.
What I noticed though is when the table is refreshed, it quickly shows the proper GRAND TOTAL, but it resets and comes back to the same thing, just the first row used. So it tries to do it ok, but seems to refresh like an AJAX move and goes back to wrong.
Hi Alan,
Sorry for late response.
We are located in Serbia and our working time is from 10:00 to 17:00 CET. business days.
From error that you get it looksile that you made view in database that is not connected in wpdatatables.
Point of the view is to make query that you need and then use is wpdatatables like SELECT * FROM view_name. If you adding additional sql statements with the view there is a chance that data will not be shown properly because wpDataTables server has problems with parsing of the query and building new queries dynamically.
Please note some this when working with the server-side processing feature:
About the Number format, yes there is some cases where you have to set proper format so layout of the numbers will shown like you need.
Best regards.
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables
Ok, thanks Milan, I totally understand. I think I have too many comments and need to summarize to make sure all is covered.
(1) The format issue I found as my mistake and did not see the format options drop down, commas and decimals are cool now.
(2) I manage to get the detail records and Grand Total ok, ONLY if I do not turn on EDITING and I do not have to use a VIEW. Once I turn on EDITING and make sure SERVER SIDE PROCESSING is OFF, then all is ok. What happens is if EDITING is on, the Grand Total only sees the first record's number. You can see the Grand Total for almost a second, then the page refreshes with the wrong results.
So if I need to edit the records it has to be done via our forms going into MYSQL. I do not have a working EDITING scenario with WPDATA when I Group By. All other tables we use in WPDATA edit fine.
(3) Problem having VIEW linked to WPDATA. I created the simple SELECT* from ClientRES which is our view and WPDATA cannot find the table ClientRES so is there something I need to do to have VIEWS work and not just tables?? This was part of my comments earlier.
Thanks!
Alan
Hi Alan,
When you turn off Editing, serverside processing will be turn off also.
Can you please tell me do you use Separate SQL connection? It is very strange that view that you make is not available in wpdatatalbes.
Can you please tell me can you make wpdatatables from tables from database where is this view?
Best regards.
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables
Hi Milan,
Yes we have done many tables, I have for two clients who I purchased WPDATA Pro for. The connection is a separate one we have been 100% successful with and I share your confusion why the VIEW is not somehow being recognized on WPDATA.
Yes, we would like to keep editing for this particular kind of table, but since the editing is not working or say keeping the Grand Total properly after an edit is made, we keep it as informational and will have to use our form plugin to do that.
Alan
I guess the theme did fix the drop down display issue but for one table not the other so I will check out the much larger table and compare it on the config and see what is different. Just found that out now when I re-tested the tables.
So that leaves the summation getting changed when editing a table with the group by and sum. The other issue above I am thinking it theme related and something I did or did not to the larger table might be fixed comparing the smaller one that is now working.
Hi Alan,
Yes we can see that table with id 6 and the issue with grand total is because serverside processing is turn on. Like we explain in previous posts this kind of issues can be fixed by making the view in database. Because we can't access to the database to check this. My suggestion is to turn off serverside processing (if you don't need editing) or to do workaround with Forms to do the editing and the table to bring in the data that you mentioned in previous posts.
Best regards.
Kind Regards,
Isidora Markovic
wpDataTables: FAQ | Facebook | Twitter | Front-end and back-end demo | Docs
Amelia: FAQ | Facebook | Twitter | Amelia demo sites | Docs
You can try our wpDataTables add-ons before purchase on these sandbox sites:
Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables