[This post is an archive from the old SQL Yoga forums]
While trying learn the strengths of SQL Yoga I have come up with the following two problems I am trying to solve with SQL Yoga. I have kept the examples small and easy to understand... initial table --> desired table.
These two examples when solved should give SQL Yoga users a lot of things to think about...
As usual I appreciate any solutions or insight on solving these two problems.
Problem 1 - Grouping and distinct value
Database - only one called Geolocation
Tables - only one called Sites
Table Sites
name gender location
Luigi M Roma
Mario M Roma
Fred M Milano
Cinzia F Cagliari
Marco M Cagliari
Jim M Roma
John M Milano
Sue F Cagliari
Maria F Paris
Giselle F Marseille
Sonia F Marseille
Jacques M Marseille
Paul M Paris
Jennifer F Manchester
Julie F New York
Christine F London
Don M London
Sam M Manchester
Colette F New York
Connie F Boston
Guy M Boston
Steve M New York
Antonio M New York
Nina F Boston
I would like to group by location and count unique genders as show by the desired table
Desired Table CitySummary
location M F total
Boston 1 2 3
Cagliari 1 2 3
London 1 1 2
Manchester 1 1 2
Marseille 1 2 3
Milano 2 0 2
New York 2 2 4
Paris 1 1 2
Roma 3 0 3
Problem 2 - group, sum and subtotal (for efficiency only one project code is shown and code 1500 shows multiple workers with their subtotals and then the Code subtotal)
Database - only one called WorkFlow
Tables - only one called timeCard
Table timeCard
Name Date Project Code Time
Teresa 11/21/2013 16505 502 1.00
Teresa 11/25/2013 16505 502 0.25
Teresa 12/2/2013 16505 502 2.50
Dan 1/6/2014 16505 1201 3.50
Kim 1/6/2014 16505 3200 1.50
Steve 1/6/2014 16505 600 0.50
Dan 1/7/2014 16505 1201 7.50
Kim 1/7/2014 16505 3200 1.50
Dan 1/8/2014 16505 1400 6.50
Ken 1/8/2014 16505 801 1.00
Teresa 1/8/2014 16505 701 0.75
Steve 1/22/2014 16505 700 0.50
Tom 1/22/2014 16505 1800 1.00
Jay 1/23/2014 16505 1500 7.00
Tom 1/23/2014 16505 1500 7.50
Desired Table
Code Name Date Time
502 Teresa 11/20/09 1.00
11/24/09 0.25
12/1/09 2.50
Teresa Total 3.75
502 Total 3.75
600 Steve 1/5/10 0.50
Steve Total 0.50
600 Total 0.50
700 Steve 1/21/10 0.50
Steve Total 0.50
700 Total 0.50
701 Teresa 1/7/10 0.75
Teresa Total 0.75
701 Total 0.75
801 Ken 1/7/10 1.00
Ken Total 1.00
801 Total 1.00
1201 Dan 1/5/10 3.50
1/6/10 7.50
Dan Total 11.00
1201 Total 11.00
1400 Dan 1/7/10 6.50
Dan Total 6.50
1400 Total 6.50
1500 Jay 1/22/10 7.00
Jay Total 7.00
Tom 1/22/10 7.50
Tom Total 7.50
1500 Total 14.50
1800 Tom 1/21/10 1.00
Tom Total 1.00
1800 Total 1.00
3200 Kim 1/5/10 1.50
1/6/10 1.50
Kim Total 3.00
3200 Total 3.00
Grand Total 42.50
Looking for SQL Yoga solutions to the following two sample p
Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Looking for SQL Yoga solutions to the following two sample p
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Looking for SQL Yoga solutions to the following two samp
These two problems are primarily SQL questions rather than SQL Yoga specific. If you know the SQL to get the results you are after then you can just set the property in a SQL Query object. I'm including some examples of how you would generate the SQL queries to get the information you are after. The properties you can set are described in the docs for sqlquery_set.I would like to group by location and count unique genders as show by the desired tablep"
Code: Select all
put sqlquery_create("Sites") into theQueryA
sqlquery_set theQueryA, "select clause", "location, COUNT(gender)"
sqlquery_set theQueryA, "conditions", "gender = :1", "M"
sqlquery_set theQueryA, "group by", "location"
Code: Select all
SELECT location, count(gender) FROM sites WHERE gender = 'M' group by location
Code: Select all
Boston 1
Cagliari 1
London 1
Manchester 1
Marseille 1
Milano 2
NewYork 2
Paris 1
Roma 3
You've really got two queries here that you then need to match up. The first query sums up the hours for each person based on code:Problem 2 - group, sum and subtotal"
Code: Select all
select Name, Code, SUM("time") FROM timeCard GROUP BY code
Code: Select all
Dan 1201 11.0
Dan 1400 6.5
Tom 1500 14.5
Tom 1800 1.0
Kim 3200 3.0
Teresa 502 3.75
Steve 600 0.5
Steve 700 0.5
Teresa 701 0.75
Ken 801 1.0
Code: Select all
put sqlquery_create("timeCard") into theQueryA
sqlquery_set theQueryA "select clause", "name, code, sum(" & quote & time & quote & ")" -- time is a reserved word in SQL
sqlquery_set theQueryA, "group by", "code"
Depending on how you are displaying the data you would then need to combine the resulting text or array (depending on whether you retrieve the data using sqlquery_retreiveAsData or sqlquery_retrieveAsArray) using whatever method you deem appropriate.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Looking for SQL Yoga solutions to the following two samp
Hi Trevor,
Thanks for the response.
I have no previous background with SQL and as such, I am learning MYSQL and SQLite by using your SQL Yoga. I hope that you and the forum will bear with me and my questions and that hopefully they will be some help to others.
Q: am I to understand correctly that if I know the SQL statement I can use it directly?
IE: SELECT location, count(gender) FROM sites WHERE gender = 'M' group by location
can be directly accepted by SQL Yoga?
I hope others in the forum will help with the two problems as I still see a lot of learning on my part.
Glen
Thanks for the response.
I have no previous background with SQL and as such, I am learning MYSQL and SQLite by using your SQL Yoga. I hope that you and the forum will bear with me and my questions and that hopefully they will be some help to others.
Q: am I to understand correctly that if I know the SQL statement I can use it directly?
IE: SELECT location, count(gender) FROM sites WHERE gender = 'M' group by location
can be directly accepted by SQL Yoga?
I hope others in the forum will help with the two problems as I still see a lot of learning on my part.
Glen
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: Looking for SQL Yoga solutions to the following two samp
Yes, you can execute SQL statements directly using dbconn_retrieveQueryAsData, dbconn_retrieveQueryAsArray or dbconn_executeSQL/dbconn_executeSQLWBindings.
If you aren't familiar with SQL at all it is probably a good idea to go through a tutorial like that at http://www.w3schools.com/sql/default.asp. Especially if you want to understand how GROUP BY and other functions work.
If you aren't familiar with SQL at all it is probably a good idea to go through a tutorial like that at http://www.w3schools.com/sql/default.asp. Especially if you want to understand how GROUP BY and other functions work.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder