• Registered: 2016-01-17
  • Posts: 139

Topic: Group Concat

Hi everyone

I have a sample Project for watering Trees which I have encountered 2 problems:

1- I need (tablegrid1 - Searchform ) Shows number of trees and also kinds of trees together: for example 20 Pine, 30 Palm

2- I need another column in  (tablegrid1 - Searchform ) to show the sum of trees in  (tablegrid1 - Mainform)

thank you guys

Post's attachments

Attachment icon Cascade.zip 335.62 kb, 30 downloads since 2024-03-14 

  • Registered: 2014-06-25
  • Posts: 863

Re: Group Concat

The data values in your example is not really helping to get a hold on what is what. Could you send a project with realistic data?

As far as I can understand it with the elements above is that you need to count how many trees grouped by kinds and concat that for #1 and sum the count of each type for #2.

Am I right?

What you count is what is in the database, not what is in grid from another form.

  • Registered: 2016-01-17
  • Posts: 139

Re: Group Concat

thanks tcoton
My project was incomplete so I had to use a sample.
here is the realistic data you asked for
and " NO " you didn't understand what I meant
I have recorded 2 instances in the attached project.
what I want is:

1) the last column in the  first line of the TableGrid1 in the Searchform  should be 20 Pine, 30 Oak
but as you can see it only shows 20, 30

2) and after the last column I need another column that shows 50 ( sum of 20 + 30 )

Am I now clear enough?

Post's attachments

Attachment icon Pesticide.zip 386.14 kb, 25 downloads since 2024-03-16 

  • Registered: 2016-01-17
  • Posts: 139

Re: Group Concat

hi
since no one is here to help
I solved the second problem myself which shows sum of the recorded trees in the last column of the tablegrid1 in the searchform.
Please some one help me with the first problem:
1) A column of the (TableGrid1 - Searchform)  should show the name of tree as well as the number of that tree.
for example:
that column should be (20 Pine, 30 Oak)

Post's attachments

Attachment icon Pesticide1.zip 386.16 kb, 22 downloads since 2024-03-17 

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Group Concat

(SELECT group_concat(kind||" "||number, ", ") 
FROM Treeno 
    left join treekind on treeno.id_treekind=treekind.id
WHERE TreeNo.id_pesticide=pesticide.id)
  • Registered: 2016-01-17
  • Posts: 139

Re: Group Concat

thank you sparrow
you're a life saver as  always