This is a single SELECT query in one of my websites:
$sql = "
SELECT SQL_CALC_FOUND_ROWS
landing_page.id AS `landing_page_id`,
landing_page.name AS `landing_page_name`,
landing_page.url,
landing_page.created_at,
landing_page.updated_at,
landing_page.deleted,
ad.id AS `ad_id`,
ad.name AS `ad_name`,
ad_group.id AS `ad_group_id`,
ad_group.name AS `ad_group_name`,
campaign.id AS `campaign_id`,
campaign.name AS `campaign_name`,
COUNT(click.id) AS `clicks`,
(SELECT
COUNT(*) AS `clicks`
FROM
click
INNER JOIN
landing_page
ON click.landing_page_id = landing_page.id
WHERE
landing_page.ad_id = MAX(ad.id)
AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "'
AND click.offer_click = 1
) AS `offer_clicks`,
COALESCE(SUM(click.cost),0) AS `cost`,
COALESCE(cost_table.cost,0) AS `flat`,
COALESCE(action_table.`count`,0) AS `actions`,
COALESCE(action_table.value, 0) AS `revenue`,
COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0) AS `profit`,
COALESCE(COALESCE(action_table.`count`,0) / COUNT(click.id), 0) * 100 AS `convrate`,
COALESCE(COALESCE(SUM(click.cost),0) / COALESCE(action_table.`count`,0), 0) AS `costconv`,
COALESCE(
(COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0))
/
COALESCE(SUM(click.cost),0)
,0) * 100 AS `roi`";
if (isset($args['ds']) && isset($args['de'])) {
$sql .= ", DATE(CONVERT_TZ(click.created_at,'GMT','$timezone')) AS `date`";
}
$sql .= "
FROM
landing_page
INNER JOIN
ad
ON landing_page.ad_id = ad.id ";
if (isset($args['ad_id']))
$sql .= " AND ad.id = " . $args['ad_id'];
$sql .= "
INNER JOIN
ad_group
ON ad.ad_group_id = ad_group.id ";
if (isset($args['ad_group_id']))
$sql .= " AND ad_group.id = " . $args['ad_group_id'];
$sql .= "
INNER JOIN
campaign
ON ad_group.campaign_id = campaign.id ";
if (isset($args['campaign_id']))
$sql .= " AND ad_group.campaign_id = " . $args['campaign_id'];
if (!empty($args['restrict_to']))
$sql .= " AND campaign.id = " . $args['restrict_to'] . " ";
$sql .= "
LEFT OUTER JOIN
click
ON click.landing_page_id = landing_page.id
AND click.created_at >= '" . $args['ds'] . "' AND click.created_at <= '" . $args['de'] . "'
AND click.offer_click = 0
AND click.deleted = 0
LEFT OUTER JOIN
(
SELECT
landing_page.id AS `landing_page_id`,
COUNT(action.id) AS `count`,
COALESCE(SUM(action.custom_amount),0) AS `value`,
DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) AS `date`
FROM
action
INNER JOIN
click
ON click.id = action.click_id
INNER JOIN
landing_page
ON landing_page.id = click.landing_page_id
INNER JOIN
ad
ON ad.id = landing_page.ad_id
INNER JOIN
ad_group
ON ad_group.id = ad.ad_group_id
INNER JOIN
campaign
ON campaign.id = ad_group.campaign_id
";
if (isset($args['ad_id'])) $sql .= " AND ad.id = " . $args['ad_id'];
if (isset($args['ad_group_id'])) $sql .= " AND ad_group.id = " . $args['ad_group_id'];
if (isset($args['campaign_id'])) $sql .= " AND campaign.id = " . $args['campaign_id'];
if (!empty($args['restrict_to'])) $sql .= " AND campaign.id = " . $args['restrict_to'];
$sql .= "
WHERE
action.deleted = 0
AND action.created_at >= '" . $args['ds'] . "' AND action.created_at <= '" . $args['de'] . "'
";
if (count($args['selected']) > 0) {
$sql .= "AND landing_page.id IN (" . implode(', ', $args['selected']) . ") ";
}
$sql .= " GROUP BY ";
if (isset($args['graph'])) {
$sql .= "DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) ";
} else {
$sql .= "landing_page.id";
}
$sql .= "
) AS `action_table`
";
if (isset($args['graph'])) {
$sql .= "ON action_table.`date` = DATE(CONVERT_TZ(click.created_at,'GMT','$timezone'))";
} else {
$sql .= "ON action_table.landing_page_id = landing_page.id";
}
$sql .= "
LEFT OUTER JOIN
(
SELECT
ad_group.id AS `ad_group_id`,
COALESCE(
CASE
WHEN ad_group.cost_type = 'monthly'
THEN ad_group.cost * $num_months
ELSE
CASE WHEN
ad_group.cost_type = 'flat'
AND ad_group.created_at >= '{$args['ds']}'
AND ad_group.created_at <= '{$args['de']}'
THEN
ad_group.cost
ELSE
0
END
END
,0) AS `cost`
FROM
ad_group
WHERE
ad_group.deleted = 0
AND (ad_group.cost_type = 'monthly' OR ad_group.cost_type = 'flat')
) AS `cost_table`
ON cost_table.ad_group_id = ad_group.id
WHERE
landing_page.deleted = 0 ";