Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm using PostgreSQL and I can't figure out a query for doing a COUNT and a SUM of two different columns. Basically I'm looking for something like a SUM(hits DISTINCT record_id)

The table:

status      record_id      hits     
     0            123         1
     0            123         1
     0            124         1
     0            125         1
     0            126         0
     1            123         0
     1            124         0
     1            125         1
     1            125         1

The desired result:

status   unique_records_count  hits_sum
     0                      4         3
     1                      3         1

Doing a

SELECT status, COUNT(DISTINCT record_id), SUM(hits)
FROM table
GROUP BY status

returns

status   unique_records_count  hits_sum
     0                      4         4
     1                      3         2
share|improve this question
2  
does hits depend on status and record_id or is ist possible that there are two records with same status and record_id but different hit? your query returns an all database system the same result sqlfiddle.com/#!1/fb8b8/1 –  miracle173 Sep 6 '12 at 5:27

3 Answers 3

Select A.status,count (A.record_id),sum(A.hits)
from 
(Select distinct status,record_id,hits from YourTable) A
group by A.status
share|improve this answer
WITH distinct_records (status, record_id, hits) (
    SELECT max(status, record_id, hits 
      FROM your_table
  GROUP BY status, record_id, hits -- group by faster than distinct
)
SELECT status, count(record_id), sum(hits) from distinct_records;

JP Chauhan's answer should give you the right answer too. Either way you are dealing with a subquery. it is worth considering that group by is often faster than distinct depending on what you are doing.

share|improve this answer

Thanks for posting this. JP Chauhan's solution also worked well with inner joins in my subquery to sum the count of a field by date. I found that my alias had an issue and had to fix that, but other wise works perfect.

select a.ptype, a.mydate, sum(a.pcount) as ProvCount
from 
   (select bkcat."Pe" ptype
         , count (distinct a.id) as pcount
         , to_char(a.upload_date,'mm/yyyy') as MYDate
    from teuplas a   
    inner join catalog as bkcat 
        on a.id = bkcat.upload_id
    where a.upload_date between '2015/05/28' and '2015/06/02'  
      and bkcat."Pe" = '...'
    group by a.upload_date, bkcat."Pe"
           , to_char(a.upload_date,'mm/dd/yyyy')
    -- order by a.upload_date desc
    -- limit 2000
    -- upload_date, (to_char(date_trunc('month',a.upload_date),'mm') 
    --             ||'/'
    --             ||to_char(date_trunc('year',a.upload_date),'yy')) 
    --                         as "Upload_Month/Year",
   ) as a
group by ptype, a.mydate
share|improve this answer
    
we edited the formatting of the code. If you prefer the old formatting, you can rollback the change. –  ypercube Jun 3 at 20:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.