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
add comment

2 Answers

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
add comment
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
add comment

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.