Coding Bear Coding Bear - 3 months ago 15
SQL Question

Count query in SQL Server

I am trying the following to go from X to Y:

tblStationary ---> X

Name Stationary
-------------------
Peter Crayon
Peter Pencil
Peter Crayon
Peter Crayon
Peter Crayon
Peter Pencil
John Crayon
John Crayon


to this query: ----> Y

Stationary Amount
-----------------
Crayon 2
Pencil 1


Crayon and Pencil must only be counted once per person. ie: When Peter has 2 pencils it must be counted as 1. So forth

I tried the following and it does not help:

SELECT
Stationary, COUNT(Name) AS Number
FROM
(SELECT * FROM dbo.tblStationary) AS t
GROUP BY
Stationary

Answer

I don't know if your RDBMS supports SELECT DISTINCT, but if it does, you can use it like that:

SELECT Stationary, COUNT(Name) AS Number
FROM (select DISTINCT * FROM dbo.tblStationary) as t
GROUP BY Stationary

So I just added a DISTINCT in the inner query.

Comments