klwillis
Posts: 428
|
| Posted: 07/17/2004, 6:04 PM |
|
I need to create a grid based on a multi-table query
where some of the fields are aggregates?
(i.e. select count(game_id) as game_account)
Some of the fields are not aggregates (i.e. player_name)
so the 'group by' clause will also be required.
Ideas or examples on how to do this are apprciated. :)
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
 |
 |
peterr
Posts: 5971
|
| Posted: 07/17/2004, 11:49 PM |
|
Do you just need some generic examples of such SQL? Possibly this would help:
http://www.google.com/search?q=sample+sql+aggregates
Generally any valid SQL query should work for a grid because CCS doesn't interpret queries - just passes them to the database and retrieves the results.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
klwillis
Posts: 428
|
| Posted: 07/18/2004, 9:27 AM |
|
Thanks Peter -
Is it possible to use the grid builder with field aggregates like count()?
The grid builder provides the ability to order a query but not group it.
This be a query I'd like to base a grid on:
select player_name, player_age, count(game_id) as games_played from players, games where players.id = games.players_id group by
player_name order by player_age
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
|
|
|
peterr
Posts: 5971
|
| Posted: 07/18/2004, 12:57 PM |
|
No, not currently.
I often like to use MS Access to visually create more complex queries, also when linked to MySQL.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
klwillis
Posts: 428
|
| Posted: 07/18/2004, 3:49 PM |
|
I was able to do this using a custom SQL query as the data source.
This has to be done manually by editing the properties of a grid form
but it works nicely and is much faster then hand-coding everything. :)
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
|
|
|
peterr
Posts: 5971
|
| Posted: 07/18/2004, 4:34 PM |
|
Yes, this is the right way to go about it.
I hope I didn't confuse you by refering to MS Access. After viewing my SQL in Access I paste it back to the SQL section of the Data Source in CCS, as you did.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
klwillis
Posts: 428
|
| Posted: 07/19/2004, 8:06 AM |
|
No - I wasn't confused. 
Thankyou.
_________________
Kevin Willis, VP/CIO
HealthCare Information Technology Specialist
http://www.nexushealthcare.com
"Fast - Convenient - Quality-Care"
Medical Software Consulting Services
Email : klwillis@nexushealthcare.com
Skype : klwillis2006 |
|
|
|
