About mysql subqueries | I want to use multiple tables to extract records that match mysql criteria - OneMinuteCode

I'm studying subqueries.

■What you want to do
I want to extract the names of employees who achieved above average sales.

■What I don't understand (what I'm hesitating about)
// Calculation of sales average
// "select avg(sale) from sales";
// Extract sales table records that have achieved above average sales
// select * from sales where sales > (select avg(sale) from sales);

I have done the above, but I don't know how to write the name of the employee above the sales average in the sql sentence based on the member_id of the sales.
I thought of the following sql statement, but is it written incorrectly in the first place?

 select * from members where member_id= 
  (select member_id from sales where sales > select avg(sale) from sales);

■ Prerequisites
sales table

 member_id | sale | month
---------+----+-----
        1|  75|    4 
        2| 200|    5
        3|  15|    6
        4| 700|    5
        5| 672|    4 
        6|  56|    8
        7| 231|    9
        8| 459|    8
        9|   8|    7
       10| 120|    4

Total Sales: 2,536
Average Sales: 253.6

members table

member_id|name
---------+--------
        1 | Tanaka
        2 | Sato
        3 | Suzuki
        4 | Tsuuchiya
        5 | Yamada
        6 | Sasaki
        7 | Harada
        8 | Takahashi
        9 | Nishida
       10 | Nakada

age table

member_id|age
---------+--------
        1|24
        2|25
        3|47
        4|55
        5|39
        6|26
        7|43
        8|33
        9|24
       10|20

■php code

<?php

require_once('functions.php');

$dbh = connectDb();

$sql="select * from members where member_id=(select member_id from sales order by sales limit1)";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$rows = $stmt-> fetchAll (PDO::FETCH_ASSOC);
// var_dump($rows);

foreach($rows as$row){
  echo "1. Name of the employee who made the biggest sales: ".$row['name']."<br>";
}
// var_dump($row['name']);


// 2. How to calculate the names of employees who have achieved above average sales
// Calculating the sales average $sql = "select avg(sale) from sales";
// Select * from sales where sales > (select avg(sale) from sales);


$sql = "select avg(sale) from sales";
$stmt = $dbh->prepare($sql);
$stmt->execute();
$avg = $stmt-> fetchAll (PDO::FETCH_ASSOC);
var_dump($avg);


echo "2. Name of employee who achieved above average sales: "."<br>";
echo "1. Total sales achieved by employees in their 30s and younger: "."<br>";

php mysql

2022-09-30 21:10

1 Answers

As for the subqueries, I think it would be better to make them easier to maintain without using subqueries if possible, so I think the following will be the case:

SELECT 
  s.member_id, sale, month, name 
from 
  sales, 
  members m
WHERE
  s.member_id = m.member_id
AND
  s.sale>(select avg(sale) from sales)

By the way, the third age table was not included in this question and sub-question, so it is not necessary.You don't need a php code to output at the same time.

2022-09-30 21:10

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656