-1

How can I add row and column grand totals in the table below? I want to get total and grand total by months.

However, I couldn't do that.

    SELECT *
FROM(
  SELECT
    YEAR(DueDate) [Year],
    CASE MONTH(DueDate)
      WHEN 1 THEN 'January'
      WHEN 2 THEN 'February'
      WHEN 3 THEN 'March'
      WHEN 4 THEN 'April'
      WHEN 5 THEN 'May'
      WHEN 6 THEN 'June'
      WHEN 7 THEN 'July'
      WHEN 8 THEN 'August'
      WHEN 9 THEN 'September'
      WHEN 10 THEN 'October'
      WHEN 11 THEN 'November'
      WHEN 12 THEN 'December'
    END as [Month],
    ProductID,
    OrderQty
  FROM Production.WorkOrder
) WorkOrders
PIVOT
(
  SUM(OrderQty)
  FOR [Month] IN (
    [January],[February],[March],[April],
    [May],[June],[July],[August],
    [September],[October],[November],[December]
  )
) AS PivotTable
ORDER BY [Year], ProductID
Velvet Dreams
  • 17
  • 7
  • 1
    Please don't SHOUT at us; we can read lowercase letters perfectly fine. Thank you. – Thom A Jun 16 '21 at 10:51
  • I am writing with google translate. It's my first time sending a message. It is true that I am a newbie here. I never understood what you mean. But still, thank you for your interest. – Velvet Dreams Jun 16 '21 at 11:32

1 Answers1

1

Stuff like this is far easier using a conditional aggregate over the restrictive PIVOT operator.

Without sample data, nor expected results, this isn't tested, but you should be able to achieve what you're after with something like this:

SELECT CASE WHEN GROUPING(DATEPART(YEAR,DueDate)) = 0 THEN
         CAST(DATEPART(YEAR,DueDate) AS varchar(50))
       ELSE 'GrandTotal' END AS [Year],
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 1 THEN OrderQty END) AS January,  --Don't use single quotes for alaises,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 2 THEN OrderQty END) AS Feburary, --it can be very confusing to read.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 3 THEN OrderQty END) AS March,    --Single quotes are for literal strings.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 4 THEN OrderQty END) AS April,    --Using ' for alias only work in the SELECT too,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 5 THEN OrderQty END) AS May,      --something like ORDER BY 'January' would not
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 6 THEN OrderQty END) AS June,     --order by data by the column aliases as 'January'.
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 7 THEN OrderQty END) AS July,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 8 THEN OrderQty END) AS August,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 9 THEN OrderQty END) AS September,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 10 THEN OrderQty END) AS October,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 11 THEN OrderQty END) AS November,
       SUM(CASE DATEPART(MONTH,DueDate) WHEN 12 THEN OrderQty END) AS December,
       SUM(OrderQty) AS GrandTotal,
       ProductID
FROM Production.WorkOrder
GROUP BY GROUPING SETS(
    (DATEPART(YEAR, DueDate), ProductID),
    (DATEPART(YEAR, DueDate)),
    ()
);
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Greetings, thank you for the support. However, this did not solve the problem. I want to get the total of rows and columns by months. How can it be done? – Velvet Dreams Jun 16 '21 at 11:30
  • You'll need to supply **consumable** sample data and expected results, @VelvetDreams . – Thom A Jun 16 '21 at 11:32
  • An image to a picture isn't helpful, @VelvetDreams . As I stated, it needs to be **consumable**, and you need to supply it as a [edit](https://stackoverflow.com/posts/68001241/edit) to your question. – Thom A Jun 16 '21 at 11:44
  • @VelvetDreams Added grand total row – Charlieface Jun 16 '21 at 12:30
  • Charlieface, Larnu Thanks for your help. How can I edit if we remove the YEAR column? Also, since we are currently in June, other months are NULL. How can I add ISNULL in this case? Best regards – Velvet Dreams Jun 16 '21 at 12:55
  • Until we get sample data and expected results, @VelvetDreams I'm not making further amendments. Though look at the documentation on how to use `ISNULL` – Thom A Jun 16 '21 at 12:58
  • Hi Larnu, Sorry I don't speak English. I translate with google translate and write to you on the same platform. Is it possible for you to state what you mean a little more clearly? Also, do I need to send a screenshot? Best regards – Velvet Dreams Jun 16 '21 at 13:08
  • Sample data, @VelvetDreams . Data that shows us what your data, in your instance, looks like. And it needs to be in a consumable format; preferable DDL and DML statements, but otherwise at least in well formatted `text`. Expected results: Data that shows what the result you expect are for the sample data you provided. Do *not* use images for either. – Thom A Jun 16 '21 at 13:22
  • If, however, you don't understand English @VelvetDreams, [so] may be the wrong community for you. It is specifically in English. There are other communities in the SE community that may be in a language you understand. Google Translate can incorrect translate sentences, meaning that what I am saying isn't what you are reading. I *can't* help you there. – Thom A Jun 16 '21 at 13:23
  • Hello Larnu, you are so right. Please excuse the newbie, unfamiliar situation. I'm really sorry. I edited the first post. Can you review it again? Best regards – Velvet Dreams Jun 16 '21 at 13:41
  • ... @VelvetDreams ... ***"Do* not *use images for either. "*** You'll need to work out the rest of this on your own at this stage I am afraid. – Thom A Jun 16 '21 at 13:44
  • Hello Larnu, Thank you very much for all the messages and all the help and especially for your sincere kindness. – Velvet Dreams Jun 16 '21 at 13:48
  • Hello, I want to ORDER BY for the above topic without breaking the column grand total. How should I do? – Velvet Dreams Jun 18 '21 at 15:56