SQL adding prices to produce table of cakes

Question

I really hope someone can help me with this sql query, been racking my brain but I know it's possible...This is my current query and produces the right format:

DECLARE
@Price1 NVARCHAR(20),
@Price2 NVARCHAR(20),
@Price3 NVARCHAR(20),
@Price4 NVARCHAR(20)

SET @Price1 = (select Price from CakeSize where SizeId = '1')
SET @Price2 = (select Price from CakeSize where SizeId = '2')
SET @Price3 = (select Price from CakeSize where SizeId = '3')
SET @Price4 = (select Price from CakeSize where SizeId = '4')

SELECT

c.Name_en as Flavor,
@Price1 as Price1,
@Price2 as Price2,
@Price3 as Price3,
@Price4 as Price4


FROM
cake a
 Left outer JOIN CakeSize b ON a.SizeId = b.SizeId
 Left outer JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
 Left outer JOIN CakeFilling d ON a.FillingId = d.FillingId
 Left outer JOIN CakeIcing f ON a.IcingId = f.IcingId
group by c.Name_en

I can't seem to get the sum of all the prices from all the tables and display.

I am able to retrieve the data but can't format it like above?

SELECT 

   c.Name_en as Flavor,
   ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as aPrice,
   ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as bPrice,
  ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as cPrice,
    ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) + ISNULL(f.Price, 0) as dPrice

FROM
   cake a
          Left Outer JOIN CakeSize b
                 ON a.SizeId = b.SizeId
          Left Outer JOIN CakeFlavor c
                 ON a.FlavorId = c.FlavorId
          Left Outer JOIN CakeFilling d
                 ON a.FillingId = d.FillingId
          Left Outer JOIN CakeIcing f
                 ON a.IcingId = f.IcingId

Instead of 4 rows for chocolate cake, I would like to have the output above; 1 row for chocolate cake. (Carrot cake is $5 less than the rest) CORRECT DATA, WRONG FORMAT Column aPrice rows 1,2,3,4 contain the correct values for chocolate cake.

(wanting the below format for each flavor)

Chocolate 18.95 18.95 23.50 38.50

This feels like a homework question. Aside from that, I feel you should also be summing it up somewhere. For a question like this it would help people answer it with some test data. Try setting it up in http://sqlfiddle.com/ for us, It'd make playing with it much easier! — LordBaconPants, Jan 15 '16 at 03:22
Not a homework question, project I am working on for work. Will try and set something up on sqlfiddle.com — ookie, Jan 15 '16 at 03:26
dude,the tables are important,just put them on so we can saw the problem clear — Raffaello.D.Huke, Jan 15 '16 at 03:30
There are a lot of unnecessary left joins. If you are not using the individual tables to check for something, the left joins don't add anything to the query or change the results in any way. — ZLK, Jan 15 '16 at 03:57
@Raffaello.D.Huke please see link for sqlfiddle.com/#!3/37096/1 — ookie, Jan 15 '16 at 05:14

score 0 · Accepted Answer · edited May 23 '17 at 12:15

ok, so you want to get total price for all flavour grouped by size, and the total price is calculated from price for each flavour + price of the filling + price for Icing, am i right ?

option 1. if the size is static and never change, you can do like this

select Flavor, SUM(aPrice) as aPrice, SUM(bPrice) as bPrice, SUM(cPrice) as cPrice, SUM(dPrice) as dPrice
from (
    select c.Name_en as Flavor, 
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as aPrice,
        0 as bPrice, 0 as cPrice, 0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '1'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice,
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as bPrice, 
        0 as cPrice, 0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '2'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice,
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as cPrice,
        0 as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '3'

    UNION ALL

    select c.Name_en as Flavor, 0 as aPrice, 0 as bPrice, 0 as cPrice, 
        ISNULL(b.Price, 0) + ISNULL(c.Price, 0) + ISNULL(d.Price, 0) +  ISNULL(f.Price, 0) as dPrice
    from cake a 
        Left JOIN CakeSize b ON a.SizeId = b.SizeId
        Left JOIN CakeFlavor c ON a.FlavorId = c.FlavorId
        Left JOIN CakeFilling d ON a.FillingId = d.FillingId
        Left JOIN CakeIcing f ON a.IcingId = f.IcingId
    where b.sizeid = '4'        
) a
where flavor is not null
group by Flavor

option 2. if the size is dynamic, you could transpose between column and row ... u can learn it here : Simple way to transpose columns and rows in Sql?

Edited : add 'UNION ALL' between third and fourth select, thankz to Sam Axe Edited : add comma in second query Edited : to add "where flavor is not null" to avoid null flavor

Ivan yours I am getting
Msg 102, Level 15, State 1, Line 17 Incorrect syntax near 'ISNULL'. Msg 102, Level 15, State 1, Line 48 Incorrect syntax near ')'. — ookie, Jan 15 '16 at 04:21
@ookie ... yup ... the query above ... produce all flavour in 1 row ... and sum the price for each size... — Ivan, Jan 15 '16 at 04:21
seems to return an extra row "Null" as the flavor?? I get errors in my sql query window on my machine? — ookie, Jan 15 '16 at 04:25
i just edit my answer to avoid null flavor ... which is happen because you have flavorID in your cake table but u dont have it in table flavor that resut null flavor ... for error syntax, its because i forget write comma in second select, and i also have update my answer to fix it ... hope this helps — Ivan, Jan 15 '16 at 04:35
Ivan, working better, one last thing, when the price show's 0.00 how can I trap this, and output say N/A??? — ookie, Jan 15 '16 at 04:49
@ookie : i think u can use `if`,example : `IF(SUM(aPrice)= 0, do_something, else_do_something) as price` — Ivan, Jan 15 '16 at 06:11

Raffaello.D.Huke · Answer 2 · 2016-01-15T04:55:51.390

i can get this done but i think there must be a better way to do it.so i just put this here for a backup answer

Fist we could create a table to store the data of your table right now and add a size column:

 SELECT 
  c.Name_en as Flavor,
  b.SizeId,
   ISNULL(b.Price,0)+ISNULL(c.Price,0)+ISNULL(d.Price,0)+ISNULL(f.Price,0) as Price -- do the price 4 times is meaningless so i cut them out just keep 1 left
 INTO #cakeP --i saw your tag as sqlserver so i create temp table like this
  FROM
  cake a
      Left Outer JOIN CakeSize b
             ON a.SizeId = b.SizeId
      Left Outer JOIN CakeFlavor c
             ON a.FlavorId = c.FlavorId
      Left Outer JOIN CakeFilling d
             ON a.FillingId = d.FillingId
      Left Outer JOIN CakeIcing f
             ON a.IcingId = f.IcingId

then we have to do 1 stupid thing:

    select 
       coalesce(a.Flavor,b.Flavor,c.Flavor,d.Flavor) as Flavor,
       a.price as aPrice,
       b.price as bPrice,
       c.price as cPrice,
       d.price as dPrice
       from 
         (select Flavor,size,price from #cakeP where SizeId=1) a
       full join (select Flavor,size,price from #cakeP where SizeId=2) b
            on a.Flavor = b.Flavor
       full join (select Flavor,size,price from #cakeP where SizeId=3) c
            on a.Flavor = c.Flavor
       full join (select Flavor,size,price from #cakeP where SizeId=4) d
            on a.Flavor = d.Flavor

and it will be done.

Note:

if the Flavor don't have size 1 then this will be screwed --ok, i find way to fix this use full join!
the Flavor have size 1 then it will be ok but this will happen:

exp: Flavor Chocolate size 1,3,4. then the data will be like: chocolate 10 null 30 40

hope this could help you

SQL adding prices to produce table of cakes

2 Answers2