Number of Orders Query...

Question

Number of Orders Query...

b_boy

SSCertifiable

Points: 5145
More actions
September 16, 2008 at 10:30 am

#198279

Hello
I have a table that consist of a unique field (CC #), and what I want to do is to retrive the number of Orders (OrderID) placed with the same CC #.
Does anyone have an idea around this, will i need a subquery?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

b_boy (9/16/2008)
Hello
I have a table that consist of a unique field (CC #), and what I want to do is to retrive the number of Orders (OrderID) placed with the same CC #.
Does anyone have an idea around this, will i need a subquery?

select cc, count(*) cnt

from tablename

group by cc

order by cc

?

* Noel

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 2

If you want the distinct number of orderIDs (if there is more than one row per orderID) I would use:

SELECT cc, COUNT(DISTINCT orderID)

FROM table

GROUP BY cc

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

b_boy SSCertifiable Points: 5145 More actions · Answer 3

Thanks you guys have helped me alot, I ran the query with you guidnace and it worked fine, but i was wandering if there is a way I can aggregate the result, with a date range.

In other words if i run the query, I wan the result set to say that a certain amout of orders had the same CC used...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

A bit more info is required... please see the link in my signature for faster, better, tested answers....

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)