Блог

Главная - Вопросы по программированию - СУММИРОВАТЬ некоторые столбцы SQL, выбранные при ОБЪЕДИНЕНИИ двух таблиц

СУММИРОВАТЬ некоторые столбцы SQL, выбранные при ОБЪЕДИНЕНИИ двух таблиц

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть этот запрос 

 SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_AS
WHERE (status = 'Paid' OR status = 'Available') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
ORDER BY Year(date), Month(date)

пример результата:

 Monthly | Annual | NumCad | Valor
      3 | 2014   |     62 | 72534
      4 | 2014   |      7 |  8253.6
      5 | 2014   |     42 | 45356.39
      6 | 2014   |     36 | 33343.19
      7 | 2014   |      5 |  4414.6

и этот запрос

 SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_PP
WHERE (status = 'Completed') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
ORDER BY Year(date), Month(date)

пример результата:

 Monthly | Annual | NumCad | Valor
      4 |   2014 |      6 | 2572.80
      5 |   2014 |      8 | 7828
      6 |   2014 |      3 | 3891.60
      7 |   2014 |      2 |  278.3

Я попытался объединить запросы:

 SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_AS
WHERE (status = 'Paid' OR status = 'Available') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
UNION
SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_PP
WHERE (status = 'Completed') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
ORDER BY Year(date), Month(date)

Но когда я делаю это, он повторяет строку с тем же месяцем… Я хочу получить СУММУ NumCad и Valor за тот же месяц

Результатом ОБЪЕДИНЕНИЯ является что-то вроде этого:

 Monthly | Annual | NumCad | Valor
      6 |   2014 |      3 |  3891.60
      6 |   2014 |     36 | 33343.19
      7 |   2014 |      5 |  4414.6
      7 |   2014 |      2 |   278.3

но я хочу это:

 Monthly | Annual | NumCad | Valor
      6 |   2014 |     39 | 37234.79
      7 |   2014 |      7 |  4692.9

Есть идея?

Ответ №1:

Сначала вам нужно выполнить объединение данных, а затем выполнить агрегацию:

 SELECT 
    Month(data.date) AS Monthly, 
    Year(data.date) AS Annual,
    COUNT(data.idcad) AS NumCad, 
    SUM(CONVERT(FLOAT, data.valorpag)) AS Valor 
FROM 
    (SELECT date, idcad, valorpag FROM PI_AS
    WHERE (status = 'Paid' OR status = 'Available') AND platform = 'Sales'
    UNION ALL
    SELECT date, idcad, valorpag  FROM PI_PP
    WHERE (status = 'Completed') AND platform = 'Sales'
    ) data
GROUP BY Year(data.date), Month(data.date)
ORDER BY Year(data.date), Month(data.date)

Ответ №2:

Это должно исправить то, что вы хотите:

 SELECT Monthly, Annual, SUM(NumCad), SUM(Valor)
FROM
 (
  SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_AS
  WHERE (status = 'Paid' OR status = 'Available') AND platform = 'Sales'
  GROUP BY Year(date), Month(date)
  UNION
  SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_PP
  WHERE (status = 'Completed') AND platform = 'Sales'
  GROUP BY Year(date), Month(date)
 ) s
GROUP BY Annual, Monthly

Ответ №3:

Превратить ваш запрос в подзапрос, а затем выбрать из него:

 select monthly, annual, sum(numcad),sum(valor)
from
(SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_AS
WHERE (status = 'Paid' OR status = 'Available') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
UNION
SELECT Month(date) AS Monthly, Year(date) AS Annual, COUNT(idcad) AS NumCad, SUM(CONVERT(FLOAT, valorpag)) AS Valor FROM PI_PP
WHERE (status = 'Completed') AND platform = 'Sales'
GROUP BY Year(date), Month(date)
)a
group by monthly, annual
 ORDER BY  monthly, annual