asked on

SQL Server - Select Distinct and Count Rows for Each Distinct Row Returned

I,m attempting to count records in one of our SQL tables that have common values in them and in that query also show the distinct values that I'm counting. I've attached a spreadsheet that contains a simple example of the content of the table and the output that I'm looking for.

As you will see in the attached spreadsheet, the table has 4 columns: assetnum, itemnum, location, binnum. Basically, I am trying to count the number of assetnums having a single combined itemnum, location and binnum.

The attached spreadsheet will give you the best example of what I am trying to do.

Thanks in advance for any suggestions / help on this.
query-example.xlsx

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cbridgman

ASKER

I think I figured this one out on my own (for once). After a lot of searching I tried the SQL shown below and it appears to have given me the answer that I was looking for. I am going to give you the points patrick because your answer came in as I was testing my answer and yours does the trick if you leave the assetnum column out.

Thanks very much for your help. I had searched around a lot and found nothing before posting the question on here but soon after I posted, I found an answer.

SELECT a.itemnum, a.location, a.binnum, count (itemnum) AS COUNT
FROM asset a
left outer join locations b
on b.location = a.location
where b.type = 'STOREROOM'
GROUP BY a.itemnum, a.location, a.binnum
order by itemnum