CODEWITHSUNDEEP
sql
Wep0n
Wep0n

Reputation: 402

Select distinct count after count?

I'll cut right to the chase: I have a select I'm currently writing with a rather lengthy where clause, what I want to do is calculate percentages.

So what I need is the count of all results and then my each distinct counts.

SELECT distinct count(*) 
FROM mytable 
WHERE mywhereclause 
ORDER BY columnIuseInWhereClause

works fine for getting each individual values, but I want to avoid doing something like 

Select (Select count(*) from mytable WHERE mywhereclause),
       distinct count(*) 
FROM mytable 
WHERE mywhereclause

because I'd be using the same where-clause twice which just seems unnecessary.

This is for OracleDB but I'm only using standard SQL syntax, nothing database specific if I can help it.

Thanks for any ideas.

Edit: Sample Data

__ID__,__someValue__
  1   |      A      
  2   |      A
  3   |      B
  4   |      C

I want the occurances of A, B, C as numbers as well as the overall count.

__CountAll__,__ACounts__,__BCounts__,__CCounts__
     4      |     2     |     1     |     1

So I can get to

   100%     |    50%    |    25%    |    25%

That last part I can probably figure out on my own. Excuse my lack of experience or even logic thinking, it's early in the morning. ;)

Edit2: I do have written a query that works but is clumsy and long as all holy heck, this one is for trying with group by.

Upvotes: 0

Views: 287

Answers (3)

Shruti
Shruti

Reputation: 190

Here's an alternative using window function:

with data_table(ID, some_value)
AS
(SELECT 1,'A' UNION ALL
 SELECT 2,'A' UNION ALL
 SELECT 3,'B' UNION ALL
 SELECT 4,'C' 
)

SELECT DISTINCT [some_value],
       COUNT([some_value]) OVER () AS Count_All, 
       COUNT([some_value]) OVER (PARTITION BY [some_value]) AS 'Counts' FROM [data_table]
ORDER BY [some_value]

The advantage is that you don't have to hard-code your [some_value]

Upvotes: 0

jarlh
jarlh

Reputation: 44796

Use case expressions to do conditional counting:

select count(*) as CountAll,
       count(case when someValue = 'A' then 1 end) as ACounts,
       count(case when someValue = 'B' then 1 end) as BCounts,
       count(case when someValue = 'C' then 1 end) as CCounts
FROM mytable 
WHERE mywhereclause

Wrap it up in a derived table to do the % part easy:

select 100,
       ACounts * 100 / CountAll,
       BCounts * 100 / CountAll,
       CCounts * 100 / CountAll
from
(
    select count(*) as CountAll,
           count(case when someValue = 'A' then 1 end) as ACounts,
           count(case when someValue = 'B' then 1 end) as BCounts,
           count(case when someValue = 'C' then 1 end) as CCounts
    FROM mytable 
    WHERE mywhereclause
) dt

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Try:

select count(*) as CountAll, 
       count(distinct SomeColumn) as CoundDistinct -- The DISTINCT goes inside the brackets
from myTable
where SomeOtherColumn = 'Something'

Upvotes: 1

Related Questions