CODEWITHSUNDEEP
sql
Rise_against
Rise_against

Reputation: 1060

SQL query distinct - group by

I am having issues with a sql query.

I have following table (I simplified it a bit):

 ChainId    ComponentId    TransmitEndpointId
   1            156               NULL
   1            156               7
   1            157               7
   2            510               6
   2            510               6
   2            511               6
   2            511               8

What I need to do is get the number of TransmitEndpointId's foreach 'unique' ComponentId in a 'unique' ChainId. So the result of the above data would be: 5 (2 unique componentId's in Chain 1 & 2 unique componentId's in Chain 2 with 2 different TransmitId's ==> NULL values don't count)

This is quite complex and have no idea on how to start with this query.

Can anybody help?

Thanks in advance.

Upvotes: 1

Views: 2330

Answers (3)

AKZap
AKZap

Reputation: 1211

I think that is what you want

Please Try this code

SELECT COUNT(DISTINCT ChainId,ComponentId,TransmitEndpointId)
FROM your_table
WHERE TransmitEndpointId IS NOT NULL

Upvotes: 0

Diego
Diego

Reputation: 36126

add the where TransmitEndpointId is not null or you will get a 0 because of the NULL

SELECT ChainId,
       ComponentId,
       count(distinct TransmitEndpointId)
FROM chain
where TransmitEndpointId is not null
GROUP BY ChainId, ComponentId

select sum(endPointNr) from 
(
SELECT count(distinct TransmitEndpointId) as     endPointNr
FROM chain
where TransmitEndpointId is not null
GROUP BY ChainId, ComponentId
) X

enter image description here

EDIT (why add TransmitEndpointId is not null)

enter image description here

Upvotes: 2

user330315
user330315

Reputation:

This will give you the unique count for each ChainID/ComponentId combination.

SELECT ChainId,
       ComponentId,
       count(distinct TransmitEndpointId)
FROM your_table
GROUP BY ChainId, ComponentId

Now you can use that inside a derived table to get the total count:

SELECT sum(distinct_count)
FROM  (
  SELECT ChainId,
         ComponentId,
         count(distinct TransmitEndpointId) as distinct_count
  FROM your_table
  GROUP BY ChainId, ComponentId
) t

Upvotes: 2

Related Questions