SQL Sum boolean values and add summarise row

Question

I have three tables, need to get one with summary for the category. Also need to add a summary row.

The main problem is I don`t know how to work with BOOLEAN type. Tried use SUM(CASE WHEN was_showed = 'TRUE' THEN 1 ELSE 0 END) but when I tried it just for table1 it returned "3" in every cases...when exactly it should be "6"

The first table1

id  was_showed
1   FALSE
2   TRUE
3   TRUE
4   TRUE
5   TRUE
6   FALSE
7   TRUE
8   TRUE
9   TRUE

the second table2

id  category
1   test1
2   test2
3   test1
4   test1
5   (null)
6   (null)
7   test1
8   test2
9   test2

the third table3

id  was_bought
2   TRUE
4   TRUE
5   TRUE
7   TRUE

The result I want to get by categories:

category | sum(was_showed) | sum(was_bougth)/sum(was_showed)
test1    |   3             |     2/3                 
test2    |   2             |     1/3
NULL     |   1             |      1

last row should be:

all | sum(was_showed) by all rows | sum(was_bougth)/sum(was_showed) by all rows

UPDATE: SQL Fiddle

@GordonLinoff i create it in SQL Fiddle, it works with boolean type... — Hytpo
– Hytpo, Commented Apr 18, 2019 at 17:38
@GordonLinoff I`ve read that the BOOLEAN and BOOL are equivalents of TINYINT(1) — Hytpo
– Hytpo, Commented Apr 18, 2019 at 17:41
@TheImpaler Thought it`s not permitted. Sure sqlfiddle.com/#!9/ae8677 — Hytpo
– Hytpo, Commented Apr 18, 2019 at 17:46

Gordon Linoff · Accepted Answer · 2019-04-18 18:03:16Z

2

If these are tinyints you can just add them up. Getting the total requires some trickery:

select coalesce(x.category, c.category) as category,
       sum(s.was_showed) as shown,
       sum(b.was_bought) as was_bought,
       sum(b.was_bought) / sum(s.was_showed) 
from table2 c left join-- categories
     table1 s
     on s.id = c.id left join -- shown (showed?)
     table3 b
     on b.id = c.id cross join
     (select null as category union all
      select 'total' as category
     ) x
group by coalesce(x.category, c.category)
order by (x.category is null) desc, c.category;

Here is a SQL Fiddle.

Or you can use with rollup:

select c.category,
       sum(s.was_showed) as shown,
       sum(b.was_bought) as was_bought,
       sum(b.was_bought) / sum(s.was_showed) 
from table2 c left join-- categories
     table1 s
     on s.id = c.id left join -- shown (showed?)
     table3 b
     on b.id = c.id 
group by c.category with rollup;

And the SQL Fiddle for this.

answered Apr 18, 2019 at 18:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hytpo Over a year ago

Thanks, with rollup unfortunately the total is also null. But i`m grateful for the first one!

The Impaler · Accepted Answer · 2019-04-18 18:30:25Z

1

The query you probably need is:

select
  *, 
  1.0 * bought / shown
from (
  select
    c.category,
    sum(case when s.was_showed = 1 then 1 end) as shown,
    sum(case when b.was_bought = 1 then 1 end) as bought
  from adShowCategoryTable c -- categories
  left join adShowsTable s on s.id = c.id -- shown (showed?)
  left join adClicksTable b on b.id = c.id -- bought
  group by c.category
) x
order by category

SQL Fiddle Example

edited Apr 18, 2019 at 18:30

answered Apr 18, 2019 at 17:42

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

3 Comments

Hytpo Over a year ago

Oh, key is pretty simple, that I should use "1" instead of "TRUE"... Thanks so much, you save my evening

Steve T Over a year ago

Why use SUM when you could filter for the records that are true and just COUNT them?

The Impaler Over a year ago

@Hytpo MySQL does not have a true boolean type. It's just a synonym of the numeric type TINYINT (a byte).

Collectives™ on Stack Overflow

SQL Sum boolean values and add summarise row

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related