Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

finding percentage from multiple tables

 
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Afternoon folks!

I have a new puzzle for my databases/ MySql class.

I have three tables of data that each have around 38K entries with multiple columns in each table.
Collaborators, Orders and RMA are the tables. (see attached screenshot)

The prompt for my assignment states that I need to use queries to analyze the number of returns by State (one of the columns in the Collaborators table)
The second half of the prompt is to analyze the percentage of the returns by product type (SKU and Description are columns in the Orders Table).

I can get a COUNT of the number of orders and the number of returns grouped by state. What I can't figure out is how to get the percentage.
I limited the results to 10 due to there being 48 states. For my queries, I will not include the LIMIT
Screenshot of Results

Screenshot of Results

Whenever I try to put both COUNTs together in the same query, I get identical results, usually, whichever is listed first.
When I get identical results, I can't get a good percentage either.

I have been beating my head around this for three days.

I can get both the total number of orders, the total number of returns, and the percentage of orders that are returned by state?

I have noticed that the top number of orders per state and the top number of returns per state do not coincide with the highest percentage of returns per state.
Some states may have fewer orders, but a higher percentage of orders returned.

From what I have seen, I would not be doing business with this fictitious company, as there is a return rate in excess of 95%  .

-Bill
SC_Ranch.jpg
[Thumbnail for SC_Ranch.jpg]
SC_Ranch1.jpg
[Thumbnail for SC_Ranch1.jpg]
SC_Ranch2.jpg
[Thumbnail for SC_Ranch2.jpg]
 
Bill Platt
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have made a little bit of progress, but I'm not sure I am achieving the results that I want yet.

I used concat in order to try and get the percentage, but I'm not there yet.
I still have the same values from the COUNT



--Bill
SC_Ranch3.jpg
[Thumbnail for SC_Ranch3.jpg]
 
Saloon Keeper
Posts: 22490
151
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
GROUP BY sku and compute COUNT(sku)  / COUNT(*)  * 100.0

I'll leave the challenge of putting that into valid SQL for you.
 
Bill Platt
Ranch Hand
Posts: 42
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, my instructor put out an announcement last night that strongly hinted at using subqueries to get the results that we're looking for. He admitted that it wasn't ideal for performance, but said that it would be easier for syntax.

That is all fine and well, but I'm still just as lost.

I appreciate the hint, Tim; however, I'm still scrambling to put it all together. It seems that I was not up to the challenge of creating valid SQL to make it work.

I have signed up for a workshop tonight, so I'm hoping that it will be enlightening.

--Bill
 
Switching from electric heat to a rocket mass heater reduces your carbon footprint as much as parking 7 cars. Tiny ad:
the value of filler advertising in 2020
https://coderanch.com/t/730886/filler-advertising
    Bookmark Topic Watch Topic
  • New Topic