Блог

Заранее спасибо за любую помощь. Я новичок в SQL и просмотрел несколько связанных потоков на этом сайте и множество других сайтов в Google, но не смог выяснить, что я делаю неправильно. Я рассмотрел подвыборки, различные варианты объединения и постоянно натыкаюсь на неправильное решение / результат. У меня есть две таблицы, к которым я пытаюсь выполнить запрос.

 Table:Doctors 
idDoctors
PracticeID
FirstName
LastName

Table: Vendor Sales
Id
ProductSales
SalesCommission
DoctorFirstName
DoctorLastName
  

Вот запрос, с которым я борюсь:

 SELECT t1.PracticeID
, SUM( t2.ProductSales ) AS Total_Sales
, COUNT( t1.LastName ) AS Doctor_Count
, COUNT( t1.LastName ) *150 AS Dues
, SUM( t2.ProductSales * t2.SalesCommission ) AS Credit
FROM Doctors AS t1
JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
GROUP BY t1.PracticeID
LIMIT 0 , 30
  

Целью запроса является вычисление чистых взносов, причитающихся Практике. Я пока не пытаюсь рассчитать чистую сумму, просто пытаюсь правильно выполнить первоначальные вычисления.

Результат (ограничен одним результатом для этого примера)

 PracticeID  Total_Sales     Doctor_Count    Dues    Credit
Practice A  16583.04               4    600     304.07360
  

Вот каким должен быть результат:

 PracticeID  Total_Sales     Doctor_Count    Dues    Credit
Practice A  16583.04               3    450 304.07360
  

Проблема в том, что общий объем продаж суммирует совокупные транзакции продаж (в данном случае 4 записи о продажах на общую сумму 16584,04). Каждая из 4 продаж имеет соответствующую ставку комиссии. Сумма кредита — это общая сумма комиссии.
Цифры продаж и кредита точны. Но количество врачей должно быть 3 (количество врачей в практике). Взносы должны составлять 450 долларов (150×3). Но, как вы можете видеть, это умножается на 4 вместо 3.
Что мне нужно изменить в запросе, чтобы получить правильные вычисления (врачи и взносы, умноженные на 3 вместо 4? Или я должен делать это по-другому? Еще раз спасибо.

В вашей схеме есть разные странности, и вы не предоставили примерные данные для обоснования заявленных вами значений.

Первая странность заключается в том, что у вас есть и имя, и фамилия врача в таблице Doctors, и в таблице Vendor Sales — однако вы присоединяетесь только по фамилии. Далее, у вас, кажется, есть столбец ID в таблице Doctors, но вы не используете его в таблице Vendor Sales для столбца joining.

Неясно, есть ли одна запись в таблице продаж поставщика для каждого врача, или их может быть несколько. Учитывая проблемы с подсчетом, которые вы описываете, мы должны предположить, что в таблице продаж поставщика может быть несколько записей на врача. Также неясно, где указан поставщик, но мы должны предположить, что это не имеет отношения к проблеме.

Итак, один набор данных, который вам нужен, — это количество врачей на практику и взносы (что составляет 150 единиц валюты на врача). Давайте сначала разберемся с этим:

 SELECT PracticeID, COUNT(*) AS NumDoctors, COUNT(*) * 150 AS Dues
  FROM Doctors
 GROUP BY PracticeID
  

Тогда нам нужны общие продажи за практику, а также кредит:

 SELECT t1.PracticeID, SUM(t2.ProductSales) AS Total_Sales,
       SUM(t2.ProductSales * t2.SalesCommission) AS Credit
  FROM Doctors AS t1
  JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
 GROUP BY t1.PracticeID
  

Эти два частичных ответа необходимо объединить в PracticeID для получения конечного результата:

 SELECT r1.PracticeID, r1.NumDoctors, r1.Dues,
       r2.Total_Sales, r2. Credit
  FROM (SELECT PracticeID, COUNT(*) AS NumDoctors, COUNT(*) * 150 AS Dues
          FROM Doctors
         GROUP BY PracticeID) AS r1
  JOIN (SELECT t1.PracticeID, SUM(t2.ProductSales) AS Total_Sales,
               SUM(t2.ProductSales * t2.SalesCommission) AS Credit
          FROM Doctors AS t1
          JOIN VendorSales AS t2 ON t1.Lastname = t2.DoctorLastName
         GROUP BY t1.PracticeID) AS r2
    ON r1.PracticeID = r2.PracticeID;
  

Я полагаю, это должно дать вам результат, к которому вы стремитесь. Но это непроверенный SQL — не в последнюю очередь потому, что вы не предоставили нам подходящие образцы данных для работы.