Sure will do and let you know, one last thing please how can we add these data in the listview
and make each row separated from the query after sorting same as I am getting the data from the googlesheet in the screenshot above?
Yes, you can do it like this
Taifun
thanks , the listview is working
I am currently trying with both arabic and english to see if its working for both or english only and will let you know
i just wanna fix the equation
select B, C, D, E, F, G, H, I, J, K, L, sum(F) group by D, E, G, H, I, J, K, L order by D, E, G, I, J, K, L
i wanna show columns B, C, D, E, F, G, H, I, J, K, L but sort and aggregate by D, E, G, I, J, K, L
What is the content of columns B, C, H?
You have ro group all columns except of those you use aggregate functions
You have sum(F), so if you do not want to aggregate B, C and H, you have to use one of the aggregate functions for these columns, too
From the documentation Query Language Reference (Version 0.7) | Charts | Google for Developers
Group By
The
group byclause is used to aggregate values across rows. A single row is created for each distinct combination of values in the group-by clause. The data is automatically sorted by the grouping columns, unless otherwise specified by anorder byclause.Note: If you use a
group byclause, then every column listed in theselectclause must either be listed in thegroup byclause, or be wrapped by an aggregation function.
Taifun
B, C, F, H
Name, Number, People number and travel price
so they should be different to get the aggrerate people number by so i wanna show all columns
B,C,D,E,F,G,H,I,J,K,L sorting by D,E,G,I,J,K,L getting aggregate F
Name, number (of what? ticket number?) and price do not matter to get the sum of people, who travel the same date and same route
One solution is to get the first name. number or price... for example
select min(B), min(C), D, E, F, G, min(H), I, J, K, L, sum(F) group by D, E, G, H, I, J, K, L order by D, E, G, I, J, K, L
Just prepare some example data and let me know, how you like to have the result so we can create the correct query
Taifun
Thanks will try that one
let me show you an example
mustafa make an order to travel to forexample Line 1 and his location is line 2 so the columns will be
name mustafa phone num 0106010 date 2024-02-12 time 19:00 people num 2 taxi line 2 line 1 group inside
name Ali phone num 0105040 date 2024-02-12 time 19:00 people num 2 taxi line 2 line 1 group inside
name taifun phone num 0109720 date 2024-01-12 time 17:00 people num 2 taxi line 2 line 1 group inside
so the query should result
taifun first as date sorting
mustafa and ali are similar date time cartype location arrive travetype and insidethecity
name taifun phone num 0109720 date 2024-01-12 time 17:00 people num 2 taxi line 2 line 1 group inside (AGGREGATE NUMBER 2)
name mustafa phone num 0106010 date 2024-02-12 time 19:00 people num 2 taxi line 2 line 1 group inside (AGGREGATE NUMBER 5)
name Ali phone num 0105040 date 2024-02-12 time 19:00 people num 3 taxi line 2 line 1 group inside (AGGREGATE NUMBER 5)
Ok, as already said, the names do not matter to get the sum of of people, who travel the same date and same route
Your result will be an overview of people, who travel the same date and same route
If you later want to find out the names of those people, send another query to the database
This is your query for the overview
select D, E, G, I, J, K, L, sum(F) group by D, E, G,, I, J, K, L order by D, E, G, I, J, K, L
Taifun
OK but how can i show name,phone number and travel price in the sorted row in the listview?
You get only one aggregated row back for all the people who travel the same date and same route
To get these detail information, send another query to the database, i.e. give me the name, phone number and price of those people who travel on 2025-01-02 the route from Egypt to United States
Taifun
if there is another way from the query it will be great so i can sort by specific columns and show all columns as if i get back some specific columns only, it will not work with me when the driver choose the order from the listview and send it to his orders so he cant send specific columns from all as all columns are related to each other and to make the driver specify the customers passengers data name, phone number people number and travel price
If rhe driver chooses one of the aggregated rows, you could send another query to the database and return all detail data
Taifun
ohh interesting so lets show me more
will i make 2 listviews with 2 query right?
so how can i match with selections for both listviews?
so when he chooses the aggregated query data and getting the full order from the another listview will be send?
thanks for your help its the last thing and i will send the aia file here to benefit others as well
Yes you can do that
In the Listview AfterPicking event just send another query to the database to get the detail data using the selection index of the selected row
select * where ...
Taifun
sorry for annoying you
can you show it for me please as you can see the not all items showing and making an error ?
i made another query button but doesn't work as well
so should i make another web got text?
as the shortest one is the data after the query and it was long same as the right one before the query
when the driver chooses the order and send it to his data its sending like that
I do not see you using gviz in your blocks? What are we doing since a few threads?
Also sending 2 Get statements at the same time does not work...
Use gviz and follow the correct sequence
- pick one of the aggregated rows
- prepare a new selext statement to get the detail data (it might be easier for you to use another web component for this)
- get the detail data back and display it in another listpicker
Taifun
let me show you
url customer
url driver
so when the driver selecting the order listview customer selction, its being deleteed from the url customer and listview customer and going to url driver and list view driver plus the driver data name,phone num and location
so it needs for all data to be shown before selection the listview customer by the driver
Let me suggest you to prepare an example test project as small as possible to understand the concept
Create a small table with 3 columns only and add sone data
name, date, number Of Passengers
Ali, 2025-01-03, 2
Berto, 2025-01-03, 3
Cesar, 2024-12-31, 1
First get the aggregated data from the database
select B, sum(C) group by B order by B
The result is
date, sum(number Of Passengers)
2024-12-12, 1
2025-01-03, 5
Then click a row, for example the second row and send another query to the database
select * where date = '2025-01-03'
to get the detail data back
name, date, number Of Passengers
Ali, 2025-01-03, 2
Berto, 2025-01-03, 3
Taifun
can you send me a sample aia file please for how to add the other query while selection the listvew so i can understand the concept?