Solved

SQL SELECT calculate probabilities

Posted on 2009-04-02
5
1,575 Views
Last Modified: 2013-11-15
I'm trying to get some probabilities for my data
(don't try to make too much sense out of this problem as I simplified it alot, the car brand is irrevelant in my analysis)
I want to calculate probabilities of the price paid given the color of the car
So for each "color" I want to count from my database how many of each "price" was paid.

The only way I can think of doing this right now is writing a script that will call SQL function and do this for me but it looks like a lot of work.

Do you know of any sql tricks I could use to help me with this?
How would I modify my select query to do this?

----------------------

i.e. all data in data base:
red 20000
yellow 20000
red 20000
black 20000

probabilities i want to calculate and retrieve:
red (20000) = 2/4
red (30000) =  0/4
red (40000) = 0/4
black (20000) = 1/4
black (30000) = 0/4
black (40000) = 0/4
yellow (20000) = 1/4
yellow (30000) = 0/4
yellow (40000) = 0/4



mysql> SELECT cr.name, pr.name, cl.name,
    ->  FROM colour cl, priceround pr, cars cr
    ->  WHERE cl.id = cr.colour
    ->  and pr.id = cr. priceround;


+--------------------------+-----------+-----------------+
| name                     | name      | name            |
+--------------------------+-----------+-----------------+
| Honda                 | red  | 20000          |
| BMW                  | yellow | 20000          |
| Toyota               | red | 20000 |
| Ford                 | white  | 20000          |
....

priceround table
+----+-----------------+
| id | name     |
+----+-----------------+
|  0 | 20000        |
|  1 | 30000 |
|  2 | 40000 |

colour table
+----+-----------+
| id | name      |
+----+-----------+
|  1 | red |
|  2 | black |
|  3 | yellow    |
0
Comment
Question by:NewtonianB
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:dirknibleck
ID: 24055845
Sure, you just want to group and count. Try:

SELECT pr.name, cl.name, COUNT(cr.name) as cars
FROM colour cl, priceround pr, cars cr
WHERE cl.id = cr.colour
and pr.id = cr. priceround

GROUP BY cl.name, pr.name
0
 

Author Comment

by:NewtonianB
ID: 24055879
GREAT!! that will save me so much code thanks alot!
is there any way I can make do the probability calculation within the select statement
so instead of outputting COUNT(cr.name) I would like COUNT(cr.name)/numberOfRecords
0
 
LVL 15

Accepted Solution

by:
dirknibleck earned 500 total points
ID: 24055909
I think the only way to do that would be to have another subquery.

ie...

Select price, color, CONCAT(cars, '/', total) FROM

(SELECT pr.name as price, cl.name as color, COUNT(cr.name) as cars
FROM colour cl, priceround pr, cars cr,
WHERE cl.id = cr.colour
and pr.id = cr. priceround

GROUP BY cl.name, pr.name) as stats, (SELECT COUNT(cars.name) as total FROM cars) as total_cars
0
 

Author Comment

by:NewtonianB
ID: 24056217
sorry I meant is there anyway of making it evaluate and actually give me the floating point value of the decimal because what this output gives me i'm then looping through it and inserting it into another table but now i'm trying to insert a string fraction to a floating point field... not going to work...
0
 

Author Closing Comment

by:NewtonianB
ID: 31566065
thank you so much you saved me alot of time
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now