SQL query

GrimStoner · Post by **GrimStoner** » 20 Dec 2007, 10:10

Hi

I need help on a SQL query...

Suppose I have a db with three fields...

1. ID
2. CellNumber
3. Transaction

I need a query to return the number of specific transactions per cellnumber. i.e... if the data were as follows :

1 0821234567 SMS
2 0821234567 MMS
3 0821234567 SMS
4 0821234567 SMS

I need the query to return the following :

1 0821234567 SMS 3
2 0821234567 MMS 1

xumwun · Post by **xumwun** » 20 Dec 2007, 10:31

you could try this.

select id, cellnumber, transaction, count(transaction) group by cellnumber

rustypup · Post by **rustypup** » 20 Dec 2007, 10:50

Code: Select all

SELECT MyTable.CellNumber, MyTable.Transaction, COUNT(MyTable.Transaction) FROM MyDb.ownr.MyTable WHERE (some_clause) GROUP BY MyTable.CellNumber, MyTable.Transaction ORDER BY MyTable.CellNumber, MyTable.Transaction

GrimStoner · Post by **GrimStoner** » 20 Dec 2007, 10:51

When I try that, I get the following error message :

You tried to execute a query that does not include the specified expression transaction as part of an aggregate function. (Error 3122)

GrimStoner · Post by **GrimStoner** » 20 Dec 2007, 10:53

Thx Rustypup... Seems like it's a workin'...

rustypup · Post by **rustypup** » 20 Dec 2007, 11:48

good news..

remember, grouping requires that you specify each distinct column... at least, in vanilla sql it does...