Mysql选择不同表中的两列[关闭]

Question

as below I select two different column(count_group and group_total) with same table and condition. I feel that this method makes the page slow. Any suggestion?

$sql3 = "SELECT SUM(mr.payment_amount) as total ,
COUNT(mr.order_id) as count_personal , 

(SELECT COUNT(DISTINCT mr.group_order_id) 
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' ) as count_group ,

(SELECT SUM(mr.payment_amount) 
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' ) as group_total 

FROM event_mgmt_registration mr 
WHERE mr.event_nid =".$nid." 
AND mr.status='completed'  
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.order_id !=''";

Answer 1

For better performance your better do a UNION to get the three results as a line each:

SELECT 
SUM(mr.payment_amount) as total ,
COUNT(mr.order_id) as count_personal
FROM event_mgmt_registration mr 
WHERE mr.event_nid =".$nid." 
AND mr.status='completed'  
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$molpaySum['latest_date']."' 
AND mr.order_id !=''

UNION ALL

(SELECT NULL, COUNT(DISTINCT mr.group_order_id)
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$molpaySum['latest_date']."' 
AND mr.group_order_id != '' )

UNION ALL

(SELECT NULL, SUM(mr.payment_amount)
FROM event_mgmt_registration mr 
WHERE mr.event_nid = ".$nid." 
AND mr.status='completed' 
AND DATE_FORMAT(FROM_UNIXTIME(`datestamp`), '%Y-%m-%d') <= '".$Sum['latest_date']."' 
AND mr.group_order_id != '' )