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
anonymous
  • 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
anonymous
  • 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!