Adding a total row to the end of query result

Question

Im trying to have a Total row at the end of query result im using MS-SQL 2012, need some help heres my query

SELECT
PropertyValue As Answer,Count(*) As rCount 
FROM 
QuestionerDetail AS Temp
where 
QuestionId = 42 and FormId = 1 
GROUP BY PropertyValue
Union All
SELECT 'Total',sum(rCount) 
FROM 
temp

Im doing something really wrong here.

The Result should be like

Answer rCount
-------------
 One     10
 Two     25
 Total   35

Thanks.

While it can be done I think it's a conceptual mistake. If you send all the data to the client anyway you should let it take care of summing it up. — Crono, Apr 03 '14 at 12:57

score 8 · Answer 1 · answered Apr 03 '14 at 12:53

You can't use the alias in another part of the union.

Try below instead:

SELECT
PropertyValue As Answer, Count(*) As rCount 
FROM 
QuestionerDetail AS Temp
where 
QuestionId = 42 and FormId = 1 
GROUP BY PropertyValue
Union All
SELECT 'Total', COUNT(*) 
FROM 
QuestionerDetail 
where 
QuestionId = 42 and FormId = 1

score 5 · Answer 2 · answered Apr 03 '14 at 12:57

Since you're using SQL Server, you could do this with a CTE.

;WITH Temp AS
(
SELECT PropertyValue As Answer, Count(*) As rCount 
FROM QuestionerDetail 
WHERE QuestionId = 42 and FormId = 1 
GROUP BY PropertyValue
)

SELECT Answer, rCount
FROM Temp
UNION ALL
SELECT 'Total' as Answer, SUM(rCount) as rCount
FROM Temp

score 3 · Answer 3 · edited Apr 03 '14 at 13:03

You can use this syntax :

WITH Temp AS(
              SELECT
                  PropertyValue As Answer
                  ,Count(*) As rCount 
              FROM QuestionerDetail AS Temp
              where QuestionId = 42 and FormId = 1 
              GROUP BY PropertyValue
             )
SELECT Answer, rCount  FROM Temp
UNION ALL
SELECT 'Total', SUM(rCount)  FROM Temp

I hope it will help you!! Good luck :)

score 3 · Answer 4 · answered Apr 03 '14 at 13:31

3

Just add WITH ROLLUP:

SELECT
PropertyValue As Answer, Count(*) As rCount 
FROM 
QuestionerDetail AS Temp
GROUP BY PropertyValue WITH ROLLUP

answered Apr 03 '14 at 13:31

dean

9,960
2
25
26

score -1 · Answer 5 · edited Feb 14 '17 at 19:46

i have created this table with joins in RAILS so i want sub total of price_value at bottom line.

+----+----------------------------+---------+-------------+
| id | name                       | item_id | price_value |
+----+----------------------------+---------+-------------+
|    | 20 packs                   | 281     | 500.0       |
|    | kingfisher-3l              | 145     | 899.0       |
|    | triple sec                 | 185     | 299.0       |
|    | jagermeister               | 179     | 599.0       |
|    | campari                    | 181     | 389.0       |
|    | craganmore 12 yrs          | 207     | 998.0       |
|    | Tandoori Jhinga            | 45      | 450.0       |
|    | Chicken Makhmali Kebab     | 43      | 320.0       |
|    | Irani Fish Tikka           | 41      | 400.0       |
|    | Indonesian Udag Sambal     | 93      | 1500.0      |
|    | Tom Yum with veg           | 3       | 160.0       |
|    | Hot & Sour with veg        | 6       | 160.0       |
|    | Salad Nicoise              | 15      | 255.0       |
|    | Lamb Gilafi Seekh          | 44      | 400.0       |
|    | Spicy wild Prwan Curry     | 108     | 500.0       |
|    | Wild Mushroom              | 2       | 160.0       |
|    | Minestrone Alla            | 1       | 320.0       |
|    | Tom Yum with Chicken       | 4       | 360.0       |
|    | Wild Mushroom              | 2       | 320.0       |
|    | Prawn Dim Sum              | 18      | 280.0       |
|    |  Seafood Biryani           | 61      | 400.0       |
|    | Coconut Panacotta          | 130     | 250.0       |
|    | singleton 12 yrs           | 269     | 11030.0     |
|    | don angel silver           | 266     | 6354.0      |
|    | johnnie walker -gold label | 272     | 13792.0     |
+----+----------------------------+---------+-------------+

this is the controller action view code

def daily_wise_report
        @result = Item.select(:name, :price_value, :item_id). joins(:order_items)
        respond_to do |format|
            format.html
            format.csv { send_data @result.to_csv }
            format.xls
        end

You should try format this. It is hard to understand. – Edgard Leal Oct 19 '16 at 12:43 — Edgard Leal, Oct 19 '16 at 12:43

Adding a total row to the end of query result

5 Answers5

Linked