Учения Adventureworks SQL Server
Using the SalesOrderHeader and SalesTerritory tables, write a query to calculate the number of transactions and total monthly amount per territory for each of the states based on the Status field. Status values are: 1 = In process ; 2 = Approved ; 3 = Backordered ; 4 = Rejected ; 5 = Shipped ; 6 = Canceled The result table must contain the following fields:
- Месяц: месяц в формате ГГГГ-ММ.
- NameTerritory : название территории
- TrProcess: транзакции в процессе (количество)
- TrApproved: одобренные транзакции (количество)
- TrBackordered: просроченные транзакции (количество)
- TrRejected: отклоненные транзакции (количество)
- TrShipped: отправлено транзакций (сумма)
- TrCanceled: транзакции отменены (количество)
- MntProcess: общая сумма в процессе
- MntApproved: общая утвержденная сумма
- MntBackordered: общая сумма задолженности
- MntRejected: общая сумма отклоненных
- MntShipped: общая сумма отправленных
- MntCanceled: Общая сумма отменена
SELECT
FORMAT(A.ShipDate, 'yyyy-MM') as Mes,
B.Name,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 1) as TrProcess,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 2) as TrApproved,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 3) as TrBackordered,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 4) as TrRejected,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 5) as TrShipped,
(SELECT count(Status) FROM [dbo].[SalesOrderHeader] where Status = 6) as TrCanceled,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 1) as MntProcess,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 2) as MntApproved,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 3) as MntBackordered,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 4) as MntRejected,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 5) as MntShipped,
(SELECT SUM(TotalDue) FROM [dbo].[SalesOrderHeader] where Status = 6) as MntCanceled
FROM [dbo].[SalesOrderHeader] A
INNER JOIN [dbo].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
GROUP BY FORMAT(A.ShipDate,'yyyy/MM')
У меня настроен этот запрос, но я не могу заставить свой запрос возвращать суммы. Я был бы очень признателен, если бы кто-нибудь мог мне помочь.
Хорошо, я постараюсь улучшить, и я улучшу поставленный вопрос, я новичок в sql, и я постараюсь это исправить.
В adventureworks2012 (у меня нет более поздней версии) таблицы относятся к продажам, а не к dbo. Вы также должны получить сообщение 8120, уровень 16, состояние 1, строка 3. Столбец «sales.SalesTerritory.Name» недействителен в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.
Ваша логика ошибочна: нет корреляции между подзапросами и ветвью/датой в основном запросе, поэтому они суммируют все. Почитайте про условную агрегацию.
Ответы 1
Ваша логика ошибочна: нет корреляции между подзапросами и ветвью/датой в основном запросе, поэтому они суммируют все. Использование условной агрегации демонстрирует разницу
SELECT
FORMAT(A.ShipDate, 'yyyy-MM') as mes,
B.Name,
(SELECT count(Status) FROM [sales].[SalesOrderHeader] where Status = 5) as TrProcess,
sum(case when status = 5 then 1 else 0 end) as trtprocesstrue
FROM [sales].[SalesOrderHeader] A
INNER JOIN [sales].[SalesTerritory] B on A.TerritoryID = B.TerritoryID
where b.name = 'northwest'
GROUP BY b.name,FORMAT(A.ShipDate, 'yyyy-MM') ;
mes Name TrProcess trtprocesstrue
---------- -------------------------------------------------- ----------- --------------
2005-07 Northwest 31465 20
2005-08 Northwest 31465 28
2005-09 Northwest 31465 21
2005-10 Northwest 31465 22
Давайте начнем с того, что забудем об особом требовании к форматированию и сосредоточимся на получении правильных данных. Во-первых, не используйте изображения для публикации важной информации. Это можно использовать, включив скрипт или скрипку, которые побудят других помочь вам. Затем вы отправили запрос, но не упомянули об этом. На самом деле, вы не задаете вопросов. Итак, какой у вас вопрос? Если ваш запрос не дает требуемых результатов, скажите об этом и объясните, ПОЧЕМУ это не так. Пока мы это делаем, добавляйте предложение ORDER BY к каждому написанному вами запросу. Без этого ряды не имеют определенного порядка — урок, который нужно усвоить.