2 Replies - 612 Views - Last Post: 10 April 2017 - 02:34 PM Rate Topic: -----

#1 wtp  Icon User is offline

  • D.I.C Regular

Reputation: 26
  • View blog
  • Posts: 319
  • Joined: 08-December 11

How can I get a Count here?

Posted 10 April 2017 - 01:14 PM

select distinct fname, lname, x
from person

I want to find the distinct number of rows for each fname and lname.

if the table contains
fname lname x

bill harris 1
bill harris 2
bill harris 3
bill harris 3 (not distinct)
john harris 1
john harris 2

I'd want the query to return

bill harris 3
john harris 2

I'm using Informix.

This post has been edited by wtp: 10 April 2017 - 01:20 PM


Is This A Good Question/Topic? 0
  • +

Replies To: How can I get a Count here?

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 13482
  • View blog
  • Posts: 53,820
  • Joined: 12-June 08

Re: How can I get a Count here?

Posted 10 April 2017 - 01:42 PM

I would figure a subquery that gets distinct on all three columns, wrapped that in a 'group by' the first two columns.

Basic outline.
select count(*), column1, column2, .. 
FROM (
SELECT distinct columna.. columnb.. 
FROM table
) group by column1, column2, .. 

Was This Post Helpful? 1
  • +
  • -

#3 wtp  Icon User is offline

  • D.I.C Regular

Reputation: 26
  • View blog
  • Posts: 319
  • Joined: 08-December 11

Re: How can I get a Count here?

Posted 10 April 2017 - 02:34 PM

Worked like a charm. What would I do without you.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1