CODEWITHSUNDEEP
sqlhiveaggregatehiveql
yanachen
yanachen

Reputation: 3753

How to add one column which is the count(*) from the table?

I have a table which includes only two columns: session_id, word_id , the table is session_word

We can get two values as the sql query below:

select count(*) as sum_word_freq from session_word
select count(distinct session_id) as sum_session_freq from session_word

I have establish a table as 

select word_id, count(*) as word_freq , count(distinct session_id) as word_session_freq
    from session_word
    group by word_id

But I want to include the result with another two columns: sum_word_freq and sum_session_freq . How can I realize it in an intact sql query.

Upvotes: 1

Views: 595

Answers (1)

leftjoin
leftjoin

Reputation: 38290

If you want aggregations by different groups in a single query then using analytic functions is the solution:

select
        word_id, 
        word_freq, 
        word_session_freq,
        --the sum for all dataset
        sum(word_freq)         over() as sum_word_freq,
        sum(word_session_freq) over() as sum_session_freq
from
(
 select word_id, 
        count(*)                   as word_freq, 
        count(distinct session_id) as word_session_freq
    from session_word
    group by word_id
)s;

Upvotes: 2

Related Questions