RhysO RhysO - 5 months ago 10
SQL Question

Select distinct of rows and show count of each value

I'm trying to select a distinct selection of

[AssetManager].[AssetType]
with a count of how many times the Id of an Asset Type is being referenced from table
[AssetManager].[Asset]
. Please see below for an example:

+-----------+-------------+
| Type Name | Asset Count |
+-----------+-------------+
| Phone | 5 |
| Desktop | 12 |
| Laptop | 22 |
+-----------+-------------+


However, the query I'm trying isn't working at all, the furthest I've got is selecting Asset titles with an inner join of their Type name (I'm not great at SQL...). Please see below for my current Query:

SELECT
[Asset].[Title] AssetTitle,
[AssetType].[Title] TypeTitle
FROM
[AssetManager].[Asset]
INNER JOIN
[AssetManager].[AssetType]
ON
[Asset].[AssetType_Id] = [AssetType].[Id]

Answer

As the comments said, all you needed to do is add a GROUP BY correctly:

SELECT
    [AssetType].[Title] TypeTitle
    , COUNT(*) [Asset Count]
FROM [AssetManager].[Asset]
     INNER JOIN [AssetManager].[AssetType]
        ON [Asset].[AssetType_Id] = [AssetType].[Id]
GROUP BY [AssetType].[Title]