Por favor, ajude no projeto de marketing da minha escola. Estou pesquisando o dia todo e tentando meu código funcionar, também tento algumas respostas aqui no stackoverflow, mas não consigo obter o resultado correto. Por favor, olhe a imagem da mesa que carreguei.
TABELAS imagem aqui por favor clique
SELECT d.user_ID, d.weekof,
TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(d.total_time))), '%H:%i') AS st_time, SUM(d.twcm) AS stwcm, SUM(d.slas) AS sslas, SUM(d.bas) AS sbas, SUM(d.iph) AS siph, SUM(d.ias) AS sias,
SUM(IF(d.lead_type = 'expireds', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS expireds,
SUM(IF(d.lead_type = 'fsbos', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS fsbos,
SUM(IF(d.lead_type = 'followup', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS followup,
SUM(IF(d.lead_type = 'buyers', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS buyers,
SUM(IF(d.lead_type = 'agents', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS agents,
SUM(IF(d.lead_type = 'circlemarketing', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS circlemarketing,
SUM(IF(d.lead_type = 'doorknocking', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS doorknocking,
SUM(IF(d.lead_type = 'investor', d.twcm + d.slas + d.bas + d.iph + d.ias, 0)) AS investors,
w.user_ID, w.weekof, SUM(w.slah) AS sslah, SUM(w.bah) AS sbah, SUM(w.sls) AS ssls, SUM(w.bls) AS sbls, SUM(w.nuc) AS snuc
FROM daily_table d
JOIN weekly_table w ON d.user_ID = w.user_ID
WHERE d.user_ID = 'kien'
GROUP BY weekof
NOVO código de atualização, mas a soma do resultado () não é a soma total correta.
SELECT w.user_ID, w.weekof, SUM(w.slah) AS sslah, SUM(w.bah) AS sbah, SUM(w.sls) AS ssls, SUM(w.bls) AS sbls, SUM(w.nuc) AS snuc,
d.user_ID, TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(d.total_time))), '%H:%i') AS st_time, SUM(d.twcm) AS stwcm, SUM(d.slas) AS sslas,
SUM(d.bas) AS sbas, SUM(d.iph) AS siph, SUM(d.ias) AS sias,
SUM(IF(d.lead_type = 'expireds', twcm + slas + bas + iph + ias, 0)) AS expireds,
SUM(IF(d.lead_type = 'fsbos', twcm + slas + bas + iph + ias, 0)) AS fsbos,
SUM(IF(d.lead_type = 'followup', twcm + slas + bas + iph + ias, 0)) AS followup,
SUM(IF(d.lead_type = 'buyers', twcm + slas + bas + iph + ias, 0)) AS buyers,
SUM(IF(d.lead_type = 'agents', twcm + slas + bas + iph + ias, 0)) AS agents,
SUM(IF(d.lead_type = 'circlemarketing', twcm + slas + bas + iph + ias, 0)) AS circlemarketing,
SUM(IF(d.lead_type = 'doorknocking', twcm + slas + bas + iph + ias, 0)) AS doorknocking,
SUM(IF(d.lead_type = 'investor', twcm + slas + bas + iph + ias, 0)) AS investors
FROM wp_weekly_goals w LEFT JOIN wp_daily_goals d ON d.weekof=w.weekof GROUP BY weekof
Fiz o meu melhor antes dos outros. Eu obtive o resultado certo. Obrigada.
SELECT w.*, d.* FROM
(SELECT user_ID, weekof, SUM(slah) AS sslah, SUM(bah) AS sbah, SUM(sls) AS ssls, SUM(bls) AS sbls, SUM(nuc) AS snuc FROM weekly_table WHERE user_ID='kien' GROUP BY weekof) w
LEFT JOIN
(SELECT user_ID, weekof, TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(total_time))), '%H:%i') AS st_time, SUM(twcm) AS stwcm, SUM(slas) AS sslas,
SUM(bas) AS sbas, SUM(iph) AS siph, SUM(ias) AS sias,
SUM(IF(lead_type = 'expireds', twcm + slas + bas + iph + ias, 0)) AS expireds,
SUM(IF(lead_type = 'fsbos', twcm + slas + bas + iph + ias, 0)) AS fsbos,
SUM(IF(lead_type = 'followup', twcm + slas + bas + iph + ias, 0)) AS followup,
SUM(IF(lead_type = 'buyers', twcm + slas + bas + iph + ias, 0)) AS buyers,
SUM(IF(lead_type = 'agents', twcm + slas + bas + iph + ias, 0)) AS agents,
SUM(IF(lead_type = 'circlemarketing', twcm + slas + bas + iph + ias, 0)) AS circlemarketing,
SUM(IF(lead_type = 'doorknocking', twcm + slas + bas + iph + ias, 0)) AS doorknocking,
SUM(IF(lead_type = 'investor', twcm + slas + bas + iph + ias, 0)) AS investors
FROM daily_table WHERE user_ID='kien' GROUP BY weekof) d ON d.weekof=w.weekof AND d.user_ID=w.user_ID
Este artigo é coletado da Internet.
Se houver alguma infração, entre em [email protected] Delete.
deixe-me dizer algumas palavras