Блог

Главная - Вопросы по программированию - Работа с датами и суммирование строк

Работа с датами и суммирование строк

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я хочу, чтобы результирующая таблица была похожа на таблицу-пример, которую я предоставил, даже если единственным предоставленным месяцем было 5, я хочу, чтобы в таблице отображались все 12 месяцев, даже если сумма должна была быть равна 0 для других месяцев.

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

 Select   
    YEAR([Date]) as [Year],
    MONTH([Date]) as [Month],
    IsNull(SUM(Amount), 0) AS TotalSales
From Sales Left Outer Join Employee
On Employee.EmployeeID = Sales.EmployeeID
    Group By YEAR([Date]), MONTH([Date])
    Order By YEAR([Date]), MONTH([Date])

Таблица сотрудников,

  -- -- ------ 
|ID|  Name   |
 -- --------- 
|1 |John Doe |
 -- --------- 
|2 |Jane Doe |
 -- ---------

Таблица продаж,

  -- ------ --------- ------- -------- 
|ID|SaleID| Date    |Amount |Quantity|
 -- ------ --------- ------- -------- 
|1 |  1   |5-14-2014|300    |12      |
 -- ------ --------- ------- -------- 
|1 |  2   |5-16-2014|600    |4       |
 -- ------ --------- ------- -------- 
|2 |  3   |5-14-2014|452    |10      |
 -- ------ --------- ------- -------- 
|2 |  4   |5-16-2014|356    |2       |
 -- ------ --------- ------- --------

Чего я хочу,

  -- --------- ----- ------ -------- -------- 
|ID|  Name   |Year |Month |Amount  |Quantity|
 -- --------- ----- ------ -------- -------- 
|1 |John Doe |2014 |5     |900     |16      |
 -- --------- ----- ------ -------- -------- 
|2 |Jane Doe |2014 |5     |808     |12      |
 -- --------- ----- ------ -------- --------

Ответ №1:

Попробуйте это

 Select S.ID,E.Name,YEAR(MAX([Date])) as [Year],MONTH(MAX([Date])) as [Month],
      IsNull(SUM(Amount), 0) AS TotalSales,IsNull(SUM(Quantity), 0) AS TotalQuantity
From Sales S Left Join Employee E On E.ID = S.ID
Group By S.ID,E.Name,YEAR([Date]), MONTH([Date])
Order By S.ID

ДЕМОНСТРАЦИЯ СКРИПКИ

Вывод:

  -- --------- ----- ------ -------- -------- 
|ID|  Name   |Year |Month |Amount  |Quantity|
 -- --------- ----- ------ -------- -------- 
|1 |John Doe |2014 |5     |900     |16      |
 -- --------- ----- ------ -------- -------- 
|2 |Jane Doe |2014 |5     |808     |12      |
 -- --------- ----- ------ -------- --------

Обновить

Если вы хотите отобразить месяцы (1-12), даже если этот месяц будет иметь значение 0

Попробуйте это

 ;With Months (Month,MonthID) 
AS
(

    select 'January' as Month , 1 as MonthID
    UNION
    select 'February' as Month , 2 as MonthID
    UNION
    select 'March' as Month , 3 as MonthID
    UNION
    select 'April' as Month , 4 as MonthID
    UNION
    select 'May' as Month , 5 as MonthID
    UNION
    select 'June' as Month , 6 as MonthID
    UNION
    select 'July' as Month , 7 as MonthID
    UNION
    select 'August' as Month , 8 as MonthID
    UNION
    select 'September' as Month , 9 as MonthID
    UNION
    select 'October' as Month , 10 as MonthID
    UNION
    select 'November' as Month , 11 as MonthID
    UNION
    select 'December' as Month , 12 as MonthID

)

SELECT R.ID,R.Name,R.[Year],M.[MonthID],M.[Month],R.TotalSales,R.TotalQUANTITY FROM
Months M LEFT JOIN
(
Select S.ID,E.Name,YEAR([Date]) as [Year],MONTH([Date]) as [Month],
      IsNull(SUM(Amount), 0) AS TotalSales,IsNull(SUM(Quantity), 0) AS TotalQuantity
From Sales S Left Join Employee E On E.ID = S.ID
Left OUTER JOIN Months ON MonthID = MONTH([Date])
Group By S.ID,E.Name,YEAR([Date]), MONTH([Date])
) R ON R.Month = M.MonthID
Order By CASE WHEN R.ID IS NULL THEN 1 ELSE 0 End,R.ID,M.MonthID

ДЕМОНСТРАЦИЯ СКРИПКИ

Комментарии:

1. Кажется, работает 🙂 Но что касается моего другого запроса, я бы хотел, чтобы результаты отображали все месяцы, даже если они не указаны как день продажи.

2. Можете ли вы отредактировать свой вопрос и предоставить пример вывода @Prosperity

3. В основном то же самое, что и ваш вывод, но в столбце Месяц будут отображаться месяцы (1-12), даже если этот месяц будет иметь значение 0

4. @Prosperity Я обновил ответ, пожалуйста, проверьте и дайте мне знать

5. Это то, что я хотел бы, но для каждого сотрудника будет отображаться от 1 до 12

Ответ №2:

 SELECT e.ID,
       e.Name,
       YEAR(s.date)AS Year,
       MONTH(s.date)AS Month,
       sum(s.amount) AS Amount,
       sum(s.quantity) AS Quantity
FROM Employee e
LEFT JOIN Sales s ON e.id=s.id
GROUP BY e.ID,e.Name,year(s.date),month(s.date)

Комментарии:

1. Я пробовал это, но это выдает ошибку, потому что e.id и e.name должна быть либо агрегированной функцией, либо в группе по

2. какую ошибку вы получаете, пожалуйста, укажите здесь, чтобы я мог вам помочь

3. @Sadikhasan Вы должны включить e.name Group by предложение for

4. @Prosperity проверьте мой обновленный ответ, теперь он работает нормально

Ответ №3:

Попробуйте это!

 select ID,Name,yaer,monht,Amount,quantity from
(
select distinct Year(Date1) as yaer,Month(Date1) as monht,rn=ROW_NUMBER()over(order by Year(Date1)) from #emp
)x,
(
select a.ID,a.Name,sum(b.Amount) as Amount,sum(b.Quantity) as quantity,rn1=row_number()over(order by a.ID) from #sales a join #emp b on a.ID=b.ID
group by a.ID,a.Name
)y
where x.rn=y.rn1

ДЕМОНСТРАЦИЯ