Coding Bear Coding Bear - 3 months ago 16
SQL Question

count query sql

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.