Counting data with SQL

Question

I have a table like the first table below (sorry if it doesn't show up correctly, I'm new to StackOverflow and haven't quite gotten the hang of how to show tables in the question). I've already received help do a count of IDs that are not duplicated (I don't mean a distinct count. A distinct count would return a result of 7 (a, b, c, d, e, f, g). I wanted it to return a count of 4 (a, c, d, f). These are the IDs that do not have multiple type codes). Now I need to take it a step further to show the count of how many times within a type code has a there is an ID with only that single type code. For example, we want to see a result like the second table below. There are 2 instances of IDs that have a single type code of 444 (c, f), there is one instance of an ID that has a single type code of 111 (a), and 222 (d).

For reference, the query that got me the count of IDs that have only one type code is

select count(*) from
 (select id from 
  mytable 
  group by id 
  having count(*) =1) t


ID|type code
a|111
b|222
b|333
c|444
d|222
e|111
e|333
e|555
f|444
g|333
g|444

Type Code|Count
111|1
222|1
444|2

In your question, don't talk about what other help you have received and what other possibilities exist. Make your question as simple to understand as possible. — Missy
– Missy, Commented Aug 3, 2016 at 21:45

JamieD77 · Accepted Answer · 2016-08-03 21:33:47Z

1

maybe this is what you're asking for?

SELECT  [type code],
        COUNT(*) [count]
FROM    mytable
WHERE   [ID] IN ( SELECT    [ID]
                  FROM      mytable
                  GROUP BY  [ID]
                  HAVING COUNT([type code]) = 1)
GROUP BY [type code]

answered Aug 3, 2016 at 21:33

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shawnt00 Over a year ago

That looks like a great guess.

jarlh Over a year ago

Note that those square brackets for delimited identifiers are product specific. ANSI SQL has double quotes, e.g. "type code".

dnoeth · Accepted Answer · 2016-08-03 22:11:29Z

1

You can solve this using a nested aggregation:

SELECT type_code, COUNT(*) 
FROM
 ( -- as this looks for a single row you can simply add the type code
   SELECT ID, MIN(type_code) as type_code
   FROM mytable
   GROUP BY  ID
   HAVING COUNT(*) = 1
 ) AS dt
GROUP BY type_code

answered Aug 3, 2016 at 22:11

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

Counting data with SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related