Summing multiple columns into one or two

[BJ9]

I'm pretty new at this and I've tried all the ways I know how to make this work. I'm trying to sum cost from categories into two columns.

Here's the code:

select sum(inventory.cost) where
sum = (select 
		sum (CASE when category.category_number >= '30000' and category.category_number <= '39999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '80000' and category.category_number <= '89999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '100000' and category.category_number <= '109999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '150000' and category.category_number <= '159999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '180000' and category.category_number <= '189999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '250000' and category.category_number <= '259999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '300000' and category.category_number <= '349999' then inventory.cost else 0 end)) as "Jewelry",
sum = (select
		sum (CASE when category.category_number >= '10000' and category.category_number <= '29999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '40000' and category.category_number <= '79999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '90000' and category.category_number <= '99999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '110000' and category.category_number <= '149999'then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '160000' and category.category_number <= '179999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '190000' and category.category_number <= '249999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '260000' and category.category_number <= '299999' then inventory.cost else 0 end),
		sum (CASE when category.category_number >= '350000' and category.category_number <= '369999' then inventory.cost else 0 end)) as "Hard"

from inventory
Join category on inventory.category_number = category.category_number
Group by inventory.cost;

What am I doing wrong? Am I even approaching it correctly. All help gratefully accepted.

Thanks
BJ

 

[ceco]

actually when I see your long query, I can't get myself thinking on it

What is your goal, and what error do you get?

 

[BJ9]

What I'm trying to do is create two columns. One called Jewelry and one called Hard.

I then want to sum the inventory.cost for each column. Unfortunately to do this I need to sum the category numbers for each column. The category numbers are not concurrent.

For example the category numbers for Jewelry are 30000-39999, 80000-89999, 100000-109999, 150000-159999, 180000-189999, 250000-259999, and 300000-349999.

All the rest of the category numbers go under Hard.

So it would look similar to this:

Jewelry Hard
Qty Cost Qty Cost
103 12,300.00 500 250,999

Thanks
BJ

 

[ceco]

SELECT (SELECT sum(inventory.cost) FROM inventory WHERE category_number BETWEEN 30000 AND 39999 OR category_number BETWEEN 80000 AND 89999 OR .....) AS Jewelry, (SELECT sum(inventory.cost) FROM inventory WHERE category_number BETWEEN 10000 AND 29999 OR .....) AS Hard;

it can be optimized, but first check if I understand the problem (you should fill the dots with the other values)

 

[BJ9]

Thanks a lot. That worked great.

BJ