SQL statement to count # of rows per value of a column

[EdAROC]

QBA - Question by example.
Looking for the number of line items per order, ideally in descending order.
Sample Data:
Ord# ItemID
1234 A
1234 B
1235 A
1236 X
1236 Y
1236 Z

Result:
1234 2
1235 1
1236 3
Better yet (descending order):
1236 3
1234 2
1235 1

 

[abaldwin]

How about this

Assuming table name = Table1
Order number field name = OrderNum
Item field name = Item

SELECT OrderNum, Count(Item) AS CountOfItem
FROM Table1
GROUP BY OrderNum
ORDER BY Count(Item) DESC;

Your basic Access Totals query utilizing Group by and order by.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[randy700]

Something like...

SELECT Ord#, Count(Ord#) AS [Line Items]
FROM [COLOR=red]YourTableName[/color]
ORDER BY Ord# DESC

Randy

 

[EdAROC]

Great!!! Thanks for the help, appreciate it.