pivot error in total

Question

0.00/5 (No votes)

See more:

Hi To all,

May i ask,

SQL

select
IFNULL(Product,'Total') as Product,
  sum(case when rmdte = '2015-12-04' then ProdVal else 0 end) `DAY 1`,
  sum(case when rmdte = '2015-12-05' then ProdVal else 0 end) `DAY 2`,
  sum(case when rmdte = '2015-12-06' then ProdVal else 0 end) `DAY 3`,
  sum(case when rmdte = '2015-12-07' then ProdVal else 0 end) `DAY 4`,
  sum(case when rmdte = '2015-12-08' then ProdVal else 0 end) `DAY 5`,
  sum(case when rmdte = '2015-12-18' then ProdVal else 0 end) `DAY 6`,
  sum(case when rmdte = '2015-12-19' then ProdVal else 0 end) `DAY 7`,
  sum(case when rmdte = '2015-12-20' then ProdVal else 0 end) `DAY 8`,
  sum(ProdVal) as Total,
  Rate,
  sum((prodval)*(Rate)) as Amount
  from
(
    select
    date(remit_date) as rmdte,
    messenger as Msgr,
    item as Product,
    item_value as ProdVal,
    b.product as RteProd,
    b.product_value as Rate
    FROM remittance a
    JOIN rate b on (a.item=b.product)
    where a.messenger = 'MESSENGER 3' and b.ratecode = '5i.35' and a.remit_date between '2015-12-18' and '2015-12-31' 
    ) as SOURCEDATA
group by Product with rollup

This code show me the total but,
under the rate column theres a value that exist which i dont need, it didnt affect the computation i made.

RESULT:

C#

Product	 DAY 1	 DAY 2	 DAY 3	 DAY 4	 DAY 5	 DAY 6	 DAY 7	 DAY 8	 Total	 Rate	 Amount
PROD 1	0	0	0	0	0	5	9	34	48	1.65	79.2
PROD 2	0	0	0	0	0	4	32	6	42	1.65	69.3
PROD 3	0	0	0	0	0	0	62	1	63	1.65	103.95
PROD 4	0	0	0	0	0	4	5	65	74	1.65	122.1
PROD 5	0	0	0	0	0	2	11	344	357	1.65	589.05
PROD 6	0	0	0	0	0	3	7	0	10	3.65	36.5
PROD 7	0	0	0	0	0	4	18	0	22	1.65	36.3
PROD 8	0	0	0	0	0	4	2	0	6	1.65	9.9
PROD 9	0	0	0	0	0	2	39	0	41	4.65	190.65
PROD 10	0	0	0	0	0	5	7	100	112	4.65	520.8
PROD 11	0	0	0	0	0	2	34	7	43	4.65	199.95
PROD 12	0	0	0	0	0	4	6	4	14	1.15	16.1
PROD 13	0	0	0	0	0	3	8	2	13	1.65	21.45
PROD 14	0	0	0	0	0	1	2	2	5	1.65	8.25
PROD 15	0	0	0	0	0	3	6	5	14	1.15	16.1
PROD 16	0	0	0	0	0	2	3	5	10	1.15	11.5
PROD 17	0	0	0	0	0	21	13	0	34	1.65	56.1
PROD 18	0	0	0	0	0	5	93	0	98	1.65	161.7
Total	0	0	0	0	0	78	371	575	1024	1.65	2278.6

thank you,
raz

Posted 21-Dec-15 16:34pm

craft_trone

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-12-21T21:44:00

Solution 1

My understanding of your post is that you don't want Rate displayed on the Total row.

You need to use the ISNULL/IFNULL function on the Product column when choosing to display the Rate column - like this

C#

select
IFNULL(Product,'Total') as Product,
  sum(case when rmdte = '2015-12-04' then ProdVal else 0 end) `DAY 1`,
  . . .

  IFNULL(Product, null, Rate),

  sum((prodval)*(Rate)) as Amount
  . . .

So when Product is null you end up with the text 'Total' and nothing in the Rate column, otherwise you get the data you need to display in both.

Posted 21-Dec-15 21:44pm

CHill60

Comments

craft_trone 25-Dec-15 20:19pm

this code gives me an error:

IFNULL(Product, null, Rate),

*/
Error Code: 1582. Incorrect parameter count in the call to native function 'IFNULL'
/*

but you were correct with your analyzation. thanks btw

CHill60 26-Dec-15 8:45am

Apologies for that - I meant to use case as well. I'll correct it when I get home. Senior moment!

craft_trone 27-Dec-15 21:08pm

ok, thank you so much chill

pivot error in total

1 solution

Solution 1

Add your solution here

Preview 0

pivot error in total

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us