Skip to main content

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.

Solved

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

Try:

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

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]


Comments

Popular posts from this blog

Conditional remove/disable/hide select list option based on another select list. All browser support required

HTML Module Account User Send me an alert when my balance... when my login... So this is existing code that someone else worked on and is no longer a resource. This little sample just changes the second select list based on the selected item in the first select list. This works just fine in FF and Chrome and after some digging I've been told that one can not hide options in a select list in IE. I've also read up on this question here but still drawing up short on getting it working for IE. Can anyone advise? Thanks. jsfiddle Solved remove instead of hide as shown in the linked post seems to work in IE. Another possible implementation is to empty the option list and add only the option s in question: var alertselect, modselect, orgalerts; var showAlerts = function (module) { alertselect.empty().append( orgalerts.filter(function () { return $(this).data('params').module === module; })); }; Of cours...

rails 4 asset pipeline vendor assets images are not being precompiled

I'm using rails 4 & ruby 1.9.3 for my application and fancybox2-rails gem, but there's a general problem with asset pipeline. If I run rake task for precompile, then everything is fine except for images in vendor/assets/images and ../gems/ruby-1.9.3-p327/gems/fancybox2-rails-0.2.1/vendor/assets/images . Images from these two folders are not being precompiled and eventually I have a problem with dead links to non-existing images. Any suggestions? Solved It seems like images are included by default only from app/assets folder. So the solution is to add this line to config/application.rb config.assets.precompile += %w(*.png *.jpg *.jpeg *.gif) It sounds Sporker can't autoload images from vendor/assets/images. 2.2 Asset Organization Pipeline assets can be placed inside an application in one of three locations: app/assets, lib/assets or vendor/assets. app/assets is for assets that are owned by the application, such as custom imag...