Looking for SQL Yoga solutions to the following two sample p

This is the place to post technical queries about SQL Yoga

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, trevordevore

Post Reply
trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 919
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Looking for SQL Yoga solutions to the following two sample p

Post by trevordevore » Fri Oct 11, 2013 2:23 pm

[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
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 919
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Looking for SQL Yoga solutions to the following two samp

Post by trevordevore » Fri Oct 11, 2013 2:26 pm

I would like to group by location and count unique genders as show by the desired tablep"
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.

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"

This would generate a query that looks like this:

Code: Select all

SELECT location, count(gender) FROM sites WHERE gender = 'M' group by location
The results would look like this:

Code: Select all


Boston 1

Cagliari 1

London 1

Manchester 1

Marseille 1

Milano 2

NewYork 2

Paris 1

Roma 3

Problem 2 - group, sum and subtotal"
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:

Code: Select all

select Name, Code, SUM("time") FROM timeCard GROUP BY code
This would give you a result that looks like this:

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

The SQL Yoga code is basically the same a the first example:

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"

The second query would get all time entries for each person. That doesn't require any special SQL so I will leave that as an exercise for the reader.

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 - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 919
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Looking for SQL Yoga solutions to the following two samp

Post by trevordevore » Fri Oct 11, 2013 2:27 pm

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
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 919
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: Looking for SQL Yoga solutions to the following two samp

Post by trevordevore » Fri Oct 11, 2013 2:27 pm

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.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

Post Reply

Return to “SQL Yoga”