CODEWITHSUNDEEP
mysqlspreadsheet
dhildreth
dhildreth

Reputation: 657

Display MySQL Pricing Chart in Spreadsheet-like Columns

I'm trying to output a friendly price table in MySQL for export/import into a spreadsheet. Let's use fruits and their price breaks as an example.

Here's a fiddle for the schema I'm referring to: http://sqlfiddle.com/#!9/c526e3/4

Simply:

Table: fruit
 id
name

Table: fruit_pricing
 id
fruit_id
min_quantity
max_quantity
price

When executing the query:

SELECT 
  F.name,
  IF(FP.min_quantity = 1, FP.price, '0') as qty_1,
  IF(FP.min_quantity = 10, FP.price, '0') as qty_10,
  IF(FP.min_quantity = 25, FP.price, '0') as qty_25,
  IF(FP.min_quantity = 50, FP.price, '0') as qty_50,
  IF(FP.min_quantity = 100, FP.price, '0') as qty_100
FROM Fruit F
  LEFT JOIN FruitPricing FP ON FP.fruit_id = F.id

It displays the results like this: MySQL result set for price breaks What I'd like to do is group the fruit names so there are only three rows: Apple, Grape, and Orange. Then, I'd like all the 0 values to be replaced with the appropriate quantities. I'm trying to get the same output as the spreadsheet in this screenshot:

Spreadsheet screenshot for price breaks

Are there any nice tricks for accomplishing this? I'm unsure of the sql-tech-speak for this particular question, making it difficult to search for an answer. I'd be happy to update my question subject if I can and somebody has a better suggestion for it.

Upvotes: 0

Views: 51

Answers (1)

Strawberry
Strawberry

Reputation: 33945

SELECT f.name
     , SUM(CASE WHEN fp.min_quantity = 1 THEN fp.price ELSE 0 END) qty_1
     , SUM(CASE WHEN fp.min_quantity = 10 THEN fp.price ELSE 0 END) qty_10
     , SUM(CASE WHEN fp.min_quantity = 25 THEN fp.price ELSE 0 END) qty_25
     , SUM(CASE WHEN fp.min_quantity = 50 THEN fp.price ELSE 0 END) qty_50
     , SUM(CASE WHEN fp.min_quantity = 100 THEN fp.price ELSE 0 END) qty_100
  FROM fruit f
  LEFT
  JOIN fruitpricing fp 
    ON fp.fruit_id = f.id
 GROUP
    BY name;

Although, if it was me, I'd probably just do the following, and handle any remaining display issues in the presentation layer...

SELECT f.name
     , fp.min_quantity 
     , SUM(fp.price) qty
  FROM fruit f
  LEFT
  JOIN fruitpricing fp 
    ON fp.fruit_id = f.id
 GROUP
    BY name
     , min_quantity;

Upvotes: 1

Related Questions