CODEWITHSUNDEEP
sqldatabasepostgresql
Bilal Hakim
Bilal Hakim

Reputation: 85

Is it possible to aggregate the results from different rows into just one, along with the count?

So I've got a small DB that has like subsections that come under a single section. Unfortunately, the DB doesn't have a "section" column and just the subsections and they have a "Inventory" column that has either "Computer" or "Laptop" in it. I've made a query that at the very least provides me with the total count of each of these "Inventory" column against each subsection.

However, I'm trying to combine the subsections into a single row and the total count of those subsections alongside it as well. Example of what I'm trying to say:

subsections inventory
a_subsec1 comp
a_subsec1 comp
a_subsec2 lap
a_subsec2 comp
a_subsec3 lap
a_subsec3 comp

What I'm currently getting:

d_sub inv_count_comp
a_subsec1 2
a_subsec2 1
a_subsec3 1

What I WANT to get:

D_SUB total_comp_count
a_sec 4

Here's the query that I'm currently running to get that second table:

SELECT DISTINCT "subsections", COUNT("inventory") FROM mytable WHERE "inventory" = 'comp' GROUP BY "subsections" ORDER BY "subsections" ASC

Thank you.

Upvotes: 1

Views: 43

Answers (1)

T. Peter
T. Peter

Reputation: 887

substring the column then you can treat all the row as same subsection.

with tb as(
select 'a_subsec1' sec,'comp' inv 
union all
select 'a_subsec1' sec,'comp' inv 
union all
select 'a_subsec2' sec,'lap' inv 
union all
select 'a_subsec2' sec,'comp' inv 
union all
select 'a_subsec3' sec,'lap' inv 
union all
select 'a_subsec3' sec,'comp' inv 

)
select msec,sum(inv_comp) total from(
       select concat(substr(sec,1,1),'_sec') as msec,
       case when inv='comp' then 1 else 0 end as inv_comp,
       tb.* 
       from tb) z
group by msec

this query might not be the one you want without some modify but main idea is same. db<>fiddle

Upvotes: 1

Related Questions