SQL questions about how calculation after joining tables and dynamic types

user_15403570 0
Viewed 36

See and test tables and my queries here: http://sqlfiddle.com/#!17/e5a87/3

I'm pretty new to SQL and I got this 3 tables:

tb1 is the data in store level

create table tb1 (id varchar(1), store_id varchar(3), sold_count int);
insert into tb1 values
('1', 's1', 40),
('2', 's2', 20),
('3', 's2', 30);

tb2 is the data about each product

create table tb2 (id varchar(1), product_id varchar(3), shelf_id varchar(4), error_type varchar(24));
insert into tb2 values
('a', 'p1', 'row1', 'A'),
('b', 'p2', 'row2', 'A'),
('c', 'p3', 'row3', 'B'),
('d', 'p4', 'row4', 'C'),
('e', 'p5', 'row4', 'C');

tb3 is a table that links store and product, a store may have multiple products

create table tb3 (tb1_id varchar(1), tb2_id varchar(1));
insert into tb3 values
('1', 'a'),
('2', 'b'),
('2', 'c'),
('3', 'd'),
('3', 'e');

now I want to write a query and get a result like this:

store_id total_sold total_shelf percentage type_A type_B type_C
s1 40 1 0.025 (1/40) 1 0 0
s2 50 (20+30) 3 0.06 (3/50) 1 1 2

I wrote a query as follow:

SELECT
  tb1.store_id,
  SUM(tb1.sold_count) AS total_sold,
  MAX(t.shelves_count) AS total_shelf_count,
  (MAX(t.shelves_count) / SUM(tb1.sold_count)) AS percentage, 
  t.A,
  t.B,
  t.C
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id
GROUP BY tb1.store_id, t.A, t.B, t.C;

It did most things correct, but I got 2 questions:

(1) The percentage column seems not working, it constantly gives me 0, when it should has a number, how can I fix it?

(2) If I have a lot of error types (not just A, B, C), and I can't really list them all specifically. Is there a way that postgresql can automatically gather the distinct types and count, and still shows a final result like this?

Really need some help, thanks in advance!! Please see the sample table and my current query here: http://sqlfiddle.com/#!17/e5a87/3

postgresql sql
1 Answers

You need to convert the type before you do division on values otherwise, PostgreSQL will use bigint to calculate when you do aggregate function.

Query 1:

SELECT
  tb1.store_id,
  SUM(tb1.sold_count) AS total_sold,
  MAX(t.shelves_count) AS total_shelf_count,
  (MAX(t.shelves_count)::decimal / SUM(tb1.sold_count)) AS percentage, 
  t.A,
  t.B,
  t.C
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id
GROUP BY tb1.store_id, t.A, t.B, t.C

Results:

| store_id | total_sold | total_shelf_count | percentage | a | b | c |
|----------|------------|-------------------|------------|---|---|---|
|       s2 |         50 |                 3 |       0.06 | 1 | 1 | 2 |
|       s1 |         40 |                 1 |      0.025 | 1 | 0 | 0 |

you can try to use pg_typeof to see the type of values.

Query 1:

SELECT
  pg_typeof(SUM(tb1.sold_count)) AS total_sold,
  pg_typeof(MAX(t.shelves_count)) AS total_shelf_count,
  pg_typeof(MAX(t.shelves_count)::decimal / SUM(tb1.sold_count)) AS total_shelf_count
FROM tb1
  JOIN (
    SELECT
      tb1.store_id AS store_id,
      COUNT(DISTINCT tb2.shelf_id) AS shelves_count,
      SUM(CASE WHEN tb2.error_type = 'A' THEN 1 ELSE 0 END) AS A,
      SUM(CASE WHEN tb2.error_type = 'B' THEN 1 ELSE 0 END) AS B,
      SUM(CASE WHEN tb2.error_type = 'C' THEN 1 ELSE 0 END) AS C
      FROM tb1
        JOIN tb3 ON tb3.tb1_id = tb1.id
        JOIN tb2 ON tb3.tb2_id = tb2.id
      GROUP BY store_id
  ) AS t ON tb1.store_id = t.store_id

[Results]:

| total_sold | total_shelf_count | total_shelf_count |
|------------|-------------------|-------------------|
|     bigint |            bigint |           numeric |
user_51760710
Related