August 15, 2005 at 9:11 pm
when i use grouping and RollUp i get error if i combine with case
name of table is TaBarang
and the field is :
[Jenis Barang],
[Nama Barang],
Harga
and then i insert data to the table, so the data is like this:
Jenis Barang Nama Barang Harga
Makanan Roti 12
Makanan Kue 13
Makanan Nasi 10
Minuman Coca-cola 5
Minuman Sprite 3
from that data, i want to make query so the result is like this:
Jenis Barang Nama Barang Harga
Makanan Roti 12
Makanan Kue 13
Makanan Nasi 10
Makanan Total 35
Minuman Coca-cola 5
Minuman Sprite 3
Minuman Total 8
the query is like this.
SELECT [Jenis Barang],
Case when (Grouping([Nama Barang]) <> 0) then [Nama Barang] else 'Total' end as 'Nama Barang',
Sum(Harga)
Group by [Nama Barang],Harga
with RollUp
i have try with this query but fail....
i use SQL Server 2000
anybody who know it?????
August 16, 2005 at 12:50 am
Here is the query:
SELECT [Jenis Barang],
CASE WHEN (GROUPING([Nama Barang]) = 0) THEN [Nama Barang] ELSE 'Total' END AS 'Nama Barang',
SUM(Harga)
FROM TaBarang
GROUP BY [Jenis Barang], [Nama Barang]
WITH ROLLUP
HAVING [Jenis Barang] IS NOT NULL
You had missed the FROM clause specifying the table, and also the GROUP BY did not include [Jenis Barang] which it needs to but did include Harga which it should not. The final HAVING clause is for excluding the row that shows the total for all rows.
August 16, 2005 at 1:39 am
i have try but still error
can you design the true????
i waiting for ur answer right now.
please.......
thanx
August 16, 2005 at 2:00 am
What error are you getting? Or is it just the wrong results? I forgot to mention that I also changed the not equals 0 in the case to equals 0. Did you try my query exactly as it is written? It should work, otherwise you need to post the DDL for your table.
August 16, 2005 at 3:17 am
by the way.....
anyone that can repair it????
August 16, 2005 at 3:34 am
I prefer to answer here instead of via PM, since then other experts can also help. Like I said, the query I provided above should really work. With the data you supplied in your original message it produces exactly the result you requested.
What happens when you run it? Do you get an error message, incorrect results or no results at all? How are you executing the query?
Also, if it is not working then please provide DDL (generate a script for the table and post here) so we can see exactly how the table is defined.
August 18, 2005 at 8:23 am
hi chris.....
my problem have been solved.
thanx chriss
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply