CODEWITHSUNDEEP
sqlpostgresql
muhnizar
muhnizar

Reputation: 327

Total row with condition "group By"

I have problem in query. I use posgresql. I need to get total row but with condition "group by"

table qwe 
----------------------------
TIPE   | VAL
----------------------------
1      | 2
2      | 3 
2      | 1
2      | 4
3      | 1
3      | 3

the result I need is

-------------------------
TIPE   | VAL | TOTAL TIPE
-------------------------
1      | 2   | 3
2      | 8   | 3
3      | 4   | 3

the query I've tried so far

select tipe, sum(val), count(tipe) "total tipe" 
from qwe group by tipe order by tipe


-------------------------
TIPE   | VAL | TOTAL TIPE
-------------------------
1      | 2   | 1
2      | 8   | 3
3      | 4   | 2

Upvotes: 1

Views: 101

Answers (4)

roman
roman

Reputation: 117561

you can try this one:

select
    tipe, sum(val), count(tipe) over() as "total tipe"
from qwe
group by tipe
order by tipe

sql fiddle demo

You see, instead of counting number of not-null tipe records inside each group, you can count number of not null tipe over the whole resultset - that's why you need over() clause. It's called window functions.

Upvotes: 2

nametal
nametal

Reputation: 1511

select
    tipe, sum(val), max(cnt) "total tipe"
from qwe
join (select count(distinct(tipe)) "cnt" from qwe) v on true
group by tipe
order by tipe

SQL-Fiddle demo

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28771

Your query is giving correct output . For desired output , i don't know why it seems you require TOTAL TIPE value fixed as 3 .

select tipe, sum(val), 3 as "total tipe" 
from qwe group by tipe order by tipe

Upvotes: 0

Daniel Casserly
Daniel Casserly

Reputation: 3480

That's a strange request, however, I would suggest that you build a view that returns just the count(tipe) of the table and then join that view in? I haven't tested that but I am pretty sure that it would work.

Upvotes: 1

Related Questions