Hi , to all i have some problem that in my sql that is ambiguty using join, after sum the data from multiple tables..

Avatar
  • updated
  • Answered

SELECT comissioned.CUSTOMERID,customer.CUSTNAME,customer.LOCATION,

customer.ADDRESS,customer.PANCHAYAT,distt.DISTTNAME,state.STATENAME,customer.PIN,customer.SACFADATE,

comissioned.COMISSIONEDDATE,SUM(ROUND(data.dayMB,2))+SUM(ROUND(data.UPTIME,2)) TOTAL_DATADAY_MB,

SUM(ROUND(datanight.NIGHTMB,2))+SUM(ROUND(datanight.UPTIME,2)) TOTAL_DATANIGHT_MB

FROM `customer`

LEFT JOIN `comissioned` ON customer.CUSTOMERID = comissioned.CUSTOMERID

LEFT JOIN `distt` ON customer.DISTTID = distt.DISTTID

LEFT JOIN `data` ON comissioned.CUSTOMERID = data.CUSTOMERID AND (data.dataDate BETWEEN "2019-11-01 00:00:00" AND "2019-11-30 00:00:00")

LEFT JOIN `datanight` ON comissioned.CUSTOMERID = datanight.CUSTOMERID AND (datanight.dataDate BETWEEN "2019-11-01 00:00:00" AND "2019-11-30 00:00:00")

LEFT JOIN `state` ON customer.STATEID = state.STATEID WHERE customer.projectid = '101' AND comissioned.CUSTOMERID= customer.CUSTOMERID

GROUP BY customerid;

This is query how to resolve this issue, when i am ruuning this query the output is comming but sum of total data is wrongly

sum. When i am running this query then output of total sum right


SELECT comissioned.CUSTOMERID,customer.CUSTNAME,customer.LOCATION,
customer.ADDRESS,customer.PANCHAYAT,distt.DISTTNAME,state.STATENAME,customer.PIN,customer.SACFADATE,
comissioned.COMISSIONEDDATE,SUM(ROUND(data.dayMB,2))+SUM(ROUND(data.UPTIME,2)) TOTAL_DATADAY_MB
FROM `customer`
LEFT JOIN `comissioned` ON customer.CUSTOMERID = comissioned.CUSTOMERID
LEFT JOIN `distt` ON customer.DISTTID = distt.DISTTID
LEFT JOIN `data` ON comissioned.CUSTOMERID = data.CUSTOMERID AND (data.dataDate BETWEEN "2019-11-01 00:00:00" AND "2019-11-30 00:00:00")
LEFT JOIN `state` ON customer.STATEID = state.STATEID WHERE customer.projectid = '101' AND comissioned.CUSTOMERID= customer.CUSTOMERID
GROUP BY customerid;

when i want both data_Day and Data_night then issue created.

Pinned replies
Avatar
Ronnie H.
  • Answer
  • Answered

Sorry you're running into trouble setting up your SQL queries. Specific coding questions are beyond the scope of our support, but I did find this resource on how to best format Select statements. If you would like help with specific coding issues, please reach out to our Managed Hosting team!

Avatar
Ronnie H.
  • Answer
  • Answered

Sorry you're running into trouble setting up your SQL queries. Specific coding questions are beyond the scope of our support, but I did find this resource on how to best format Select statements. If you would like help with specific coding issues, please reach out to our Managed Hosting team!