Looking for SQL Yoga solutions to the following two sample p
Posted: 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
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