Query Assistance Humbly Requested

1

Hello everyone,

I hope someone can help me out with this. I have a table that has some fields named SpecialNumber, BuyOrSell, TranType, Amount. Each SpecialNumber might be in the table once, or multiple times. Can a query be written that shows how many times each SpecialNumber is in the table?

If I have the following values in the table:

12345, 'Buy', 'Cash', $100.00
11111, 'Sell', 'Credit', $89.00
33333, 'Sell', 'Credit', $44.00
22222, 'Buy', 'Credit', $55.00
22222, 'Sell', 'Cash', $22.00

I need to know that 12345 is counted as once, 11111 is counted as once 33333 is counted as once, and that 22222 is counted as twice. And if possible just show the ones for when the count is more than once. Is this possible? If so, will someone give me a hand with this? PLEASE

2

I think that you need more that this,but this will give you what you want

select SpecialNumber, count(1)
  from Table
group by SpecialNumber
having count(1) > 1
1 Like
3

This super gives me what I want. Thank you soooo much!! How do I interpret or read "count(1)"? I don't understand this,or why this works.

4

And if possible just show the ones for when the count is more than once. Is this possible?

SELECT tn.*, sn_multiple.SpecialNumberCount
    FROM dbo.table_name tn
    INNER JOIN (
        SELECT SpecialNumber, COUNT(*) AS SpecialNumberCount
        FROM dbo.table_name
        GROUP BY SpecialNumber
        HAVING COUNT(*) > 1
    ) AS sn_multiple ON sn_multiple.SpecialNumber = tn.SpecialNumber
1 Like
5

This is great. Thanks!