How to merge columns

[faccorp]

Hi,

I have a table that contains up to 3 columns that may or may not contain price data for up to 3 SKU's

ID   SKU1   PRICE1   SKU2   PRICE2   SKU3   PRICE3
--------------------------------------------------
001  AAAA    10.00   BBBB    12.00   CCCC   15.00
002  BBBB    12.50
003  DDDD    20.00   AAAA    8.00

Each record will have at least a value for SKU1 but may also have a value for SKU2 and possibly a value for SKU3.

I am trying to write a query that will give me a count of how many times each SKU has been used and the total amount charged for each SKU. So the results for the query would look like:

SKU  CNT   TOTAL
-----------------
AAAA   2    18.00
BBBB   2    22.50
CCCC   1    15.00
DDDD   1    20.00

Any help would be appreciated.

 

[swampBoogie]

select sku,count(*) as cnt, sum(price) as total
from (
   select SKU1 as sku,PRICE1 as price
     from t
   union all
   select sku2,price2
     from t
    where sku2 is not null
   union all
   select sku3,price3
     from t
    where sku3 is not null)dt
group by sku

Anyone told you that you have a bad database desigm?

 

[faccorp]

Thanks,

What is the reason for the 'dt' toward the end of the query?

Also, why is this a bad design?

Each invoice can have at least 1 but never more than 3 SKU's. There is more information on each record such as customer name and other information - but I left it out for simplicity.

Thanks

 

[faccorp]

It looks like 'dt' means 'derived table'. Ok this makes sense. The query works as intended. Thank You.

I welcome discussion though on why this is a bad database design.


Thanks

 

[checkai]

when you write a query like swampboogie has, you need to give the (select...) a table name alias...so it could be anything...doesn't mean derived table.

"...we both know I'm training to become a cagefigher...see what happens if you try 'n hit me..."

 

[vongrunt]

I welcome discussion though on why this is a bad database design.

Why... because SKU1, SKU2 and SKU3 are repeatable columns. Both have the same purpose.

The only advantage of such design is easier editing/reporting. Everything else is plain ugly; query you wanted is just the tip of an iceberg.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Catadmin]

Vongrunt is correct. You really should create another table that has the columns SKU and Price with some sort of link back to your invoice table. You could do it several ways.

The way some people use is to create three tables. One for invoices, one for skus and one to connect the two together on their primary keys. This would be, IMHO, the best design, because then you only have to list the Skus and their prices once.

Or, you could create a second table that has Sku, Price, InvoiceNo. This isn't so great a design because you're repeating data (who knows how many times Sku1 will get re-used on a sample of 50 invoices), but will link without having your repeating columns.

Either way will solve your problem of counting the # of times the SKU gets used and what the total price is.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[faccorp]

Thanks.

I do already have 2 tables. One table just shows a list of the SKU's and the standard price. The user selects from this list and has the ability to change the price when the invoice is created.

In an earlier design (I was using MSAccess - now designing in SQL Server) I did have the three tables. One for SKU's, one for invoice information, and then a link to a table of details for the invoice (i.e what SKU's were used and the price). Since the max SKU's was never more than 3, I thought a better design would be to just have 1 table for the invoices and leave SKU2 and SKU3 blank if they weren't used.

I thought this would be a simpler design.

Are you all in agreement that this would eventually lead to more complex queries and less flexibility down in the future?


Thanks for your comments.

 

[vongrunt]

FYI take a look at this. Figure #7 is literally identical to your case.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Catadmin]

Are you all in agreement that this would eventually lead to more complex queries and less flexibility down in the future?

Yes, I am.

Although, if you enjoy challenges and don't mind the extra work, by all means, leave it the way it is. You could use it more easily as job security than anything else. @=) (please take last statement with tongue-n-cheek).



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!