How to Count Distinct Records

count distinct records (all columns) not working

select count(*)
from
(
   select distinct * from your_table
) x

Count distinct values depending on group

You would use count(distinct):

select "group", count(distinct id)
from t
group by "group";

Note that group is a very poor name for a column because it is a SQL keyword. Hopefully the real column name is something more reasonable.

Query for count of distinct values in a rolling date range and country

The reason that country doesn't exist is that we can only SELECT columns from the tables that are in a FROM section of a query. The nested subquery selects from tbl but that is not available to the main, outer query, which only selects from the generated table g. g only has a date column, so that's the only column the outer query can select on directly.

Another issue with the query is that the COUNT is not factoring in countries.

For this I'd use basic join to query every row for every date range, then do a count distinct for every date + country. You can use an INNER join to remove days that have no entries or LEFT OUTER to return rows of {date}, nil,nil if there are no entries for that date range. Something like:

SELECT g.date
     , tbl.country
     , COUNT(DISTINCT(tbl.email))
    FROM  (SELECT generate_series(timestamp '2012-01-01'
                                , timestamp '2012-01-06'
                                , interval  '1 day')::date) AS g(date)
    INNER JOIN tbl ON (tbl.date BETWEEN g.date - 2 AND g.date)

GROUP BY 1,2

How to count distinct values from a table in Oracle

This is what the HAVING clause is intended for: filtering data on your aggregation.

SELECT user
      ,COUNT(DISTINCT state)
  FROM temp_table
 GROUP BY user
HAVING COUNT(DISTINCT state) > 1;

If the HAVING clause makes you uncomfortable, then you could do this nesting queries as you showed, with just a small tweak.

SELECT user
  FROM (SELECT user
              ,COUNT(DISTINCT state) AS state_cnt
          FROM temp_table
         GROUP BY user) t
 WHERE state_cnt > 1;

In either case, you group by user and count states to get the answer of "states per user."

count distinct values while left joining

You can group by people.id and count the distinct companies:

SELECT p.id, p.name, 
       COUNT(DISTINCT r.company) companies 
FROM people p LEFT JOIN reports r
ON p.id = r.people_id
GROUP BY p.id;

I assume the id is the primary key of the table people.

Calculate distinct values per day that resets each month (Big Query)

If you want to count "unique" stores cumulatively within a month, I would recommend using row_number() to determine the first time a store appears:

select order_date, countif(seqnum = 1)
from (select t.*,
             row_number() over (partition by store_id, date_trunc(order_date, month)
                                order by order_date
                               ) as seqnum
      from t
     ) t
group by order_date;

Distinct Count with data from another table

You must join users with LEFT joins to tags and pictures and aggregate:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
GROUP BY u.id, u.name

If you want the result for a specific user only:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
WHERE u.id = ?
GROUP BY u.id, u.name -- you may omit this line, because SQLite allows it

Or with a correlated subquery:

SELECT u.id, u.name, 
       (
         SELECT COUNT(DISTINCT p.album_id)
         FROM tags t INNER JOIN pictures p 
         ON p.id = t.picture_id
         WHERE t.user_id = u.id 
       ) counter
FROM users u
WHERE u.id = ?

Replace ? with the id of the user that you want.

---

Related Topics