SELECT
`table1`.`model`,
`table1`.`quantity`,
SUM(table2.sale_quantity)
FROM `table1`
JOIN `table2` ON `table1`.`model`=`table2`.`model`
WHERE
`table1`.`manufacturer_id` = 'apple'
AND `table1`.`quantity` < 20
GROUP BY `table1`.`model`, `table1`.`quantity`;-- group by s.ID,t.TIME,s.NAME,t.M8_SUM ,t.TRAFF_SUM,t.PMAC_SUM
GROUP BY s.ID, t.TIME, s.NAMECREATE TABLE Cities (
id int primary key auto_increment,
city varchar(64)
);
CREATE TABLE CityDistnce (
city1 int,
city2 int,
distance int,
foreign key (city1) references Cities(id),
foreign key (city2) references Cities(id),
index (city1, city2)
);SELECT `user_id`
FROM `users`
WHERE status = 'admin'
ORDER BY `connected_users` ASC
LIMIT 1;select
m.match_id,
min(opponent_id) as op1, max(opponent_id) as op2
from match_opponents op
inner join matches m on op.match_id = m.match_id
where m.status = "started"
group by m.match_id;update `test` set `answer` = '{"test":["qwerty","\\"test\\""]}' ;SELECT
COALESCE(name, 'Total') AS name,
SUM(salary * percent * 3) AS result
FROM salaries
GROUP BY name
WITH ROLLUP;<?php
$query = "SELECT
COALESCE(name, 'Total') AS name,
SUM(salary * percent * :multiplayer) AS result
FROM salaries
GROUP BY name
WITH ROLLUP;";
$stmt = $pdo->prepare($query);
$stmt->execute(['multiplayer' => 3]);
$result = $stmt->fetchALL(PDO::FETCH_ASSOC);
var_export($result);select point, count(distinct guest_id) as guest_count
from sales
group by point;SELECT *
FROM `catalog_national_finals` as A
LEFT JOIN `voting_data_national_finals` as B ON (A.id = B.id_participant)
WHERE A.year = '$year' AND (B.id_user = '$id_user' OR B.id_user IS NULL)SELECT * FROM (
SELECT
product.*,
prices.price ,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY timestamp DESC) rn
FROM product
JOIN prices ON prices.product_id = product.id
) prices WHERE rn = 1
;select
age_hobbies.age,
age_hobbies.hobbies
from (
select age, hobbies, count(*) cnt
from user_hobbies
group by age, hobbies
) age_hobbies
join (
select age, max(cnt) maxcnt
from (
select age, hobbies, count(*) cnt
from user_hobbies
group by age, hobbies
) h group by age
) max_age_hobbies on
age_hobbies.age = max_age_hobbies.age and
age_hobbies.cnt = max_age_hobbies.maxcnt;select
id,
title,
start,
end,
timediff(end, current_time) as remain_time
from lessons
where current_time between start and end
;select *
from (
select age, hobbies, count(*) cnt
from user_hobbies
group by age, hobbies
) age_hobbies
join (
select age, max(cnt) maxcnt
from (
select age, hobbies, count(*) cnt
from user_hobbies
group by age, hobbies
) h group by age
) max_age_hobbies on
age_hobbies.age = max_age_hobbies.age and
age_hobbies.cnt = max_age_hobbies.maxcnt;
$bd->Query("SELECT COUNT(*) FROM users_nykfageubf WHERE p = '$p' AND promo_new=1"))
$bd->Query("UPDATE users_nykfageubf SET balance=balance+10, promo_new = 0 WHERE p = '$p' AND promo_new=1");$stmt = $bd->Query("SELECT 1 FROM users_nykfageubf WHERE p = '$p' AND promo_new=1 LIMIT 1");
if ($stmt->rowCount() > 0) {
...........
}update users_match
set usr2 = case
when usr1 = 1 then 5
when usr1 = 2 then 6
when usr1 = 3 then 7
else usr2
end;select *
from orders
join (
select order_id, sum(price) paid
from transactions
group by order_id
) paiments on
paiments.order_id = orders.id and
(orders.price + orders.delivery_price) =< paiments.paid;select *
from orders
left join (
select order_id, sum(price) paid
from transactions
group by order_id
) paiments on
paiments.order_id = orders.id
where (orders.price + orders.delivery_price) > coalecse(paiments.paid, 0);SELECT
*
FROM (
SELECT
url_id,
status,
updated_at,
ROW_NUMBER() OVER (PARTITION BY url_id ORDER BY updated_at DESC) rn
FROM urls_checks
ORDER BY urls_checks DESC
) checks
JOIN urls on urls.id = checks.url_id
WHERE rn = 1;update table1
join table2 on table1.name = table2.name
set table1.total = (total - total2)
;