Solved

need select with count

Posted on 2007-03-31
2
163 Views
Last Modified: 2010-08-05
I have a query:
Select Distinct email, quote, product, DATE From tblTest
that generates the following type of data...
a_s@company.org      102297      prod1            2/26/2007
a_k@company.org      60448      prod2      2/8/2007
a_k@company.org      72454      prod2      2/8/2007
a_k@company.org      133024      prod2      3/19/2007
a_k@company.org      133030      prod2      3/19/2007
a_k@company.org      134622      prod2      3/20/2007
a_k@company.org      134936      prod2      3/20/2007
a_k@company.org      134981      prod2      3/20/2007
a_l@company.org      987610      prod2      2/16/2007
a_l@company.org      84018      prod1            2/9/2007
a_l@company.org      85573      prod2      2/13/2007
A_T@company.org      32334      prod2      1/14/2007
A_T@company.org      88763      prod2      3/13/2007
A_T@company.org      123372      prod2      3/22/2007
a_c@company.org      62272      prod1            1/23/2007
a_c@company.org      84560      prod3      2/9/2007
a_c@company.org      100968      prod1            2/23/2007
a_c@company.org      135166      prod3      3/20/2007
A_L@company.org      105942      prod4      2/28/2007
a_j@company.org      37878      prod2      12/28/2006

How can I get the count of distinct quotes,product,date per email in the same table? I know how to get it in a different table with email and count...
but something like this:
a_s@company.org      102297      prod1            2/26/2007      1
a_k@company.org      60448      prod2      2/8/2007      7
a_k@company.org      72454      prod2      2/8/2007      7
a_k@company.org      133024      prod2      3/19/2007      7
a_k@company.org      133030      prod2      3/19/2007      7
a_k@company.org      134622      prod2      3/20/2007      7
a_k@company.org      134936      prod2      3/20/2007      7
a_k@company.org      134981      prod2      3/20/2007      7
a_l@company.org      987610      prod2      2/16/2007      3
a_l@company.org      84018      prod1            2/9/2007      3
a_l@company.org      85573      prod2      2/13/2007      3
A_T@company.org      32334      prod2      1/14/2007      3
A_T@company.org      88763      prod2      3/13/2007      3
A_T@company.org      123372      prod2      3/22/2007      3
a_c@company.org      62272      prod1            1/23/2007      4
a_c@company.org      84560      prod3      2/9/2007      4
a_c@company.org      100968      prod1           2/23/2007      4
a_c@company.org      135166      prod3      3/20/2007      4
A_L@company.org      105942      prod4      2/28/2007      1
a_j@company.org      37878      prod2      12/28/2006      1

Is it possible even?


0
Comment
Question by:acdagirl
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18829084
select t.*, ( select count(*) from tblTest i where i.email = t.email )
from tblTest t
0
 

Author Comment

by:acdagirl
ID: 18829121
I modified to to give me the fields of interest as my tblTest contains more fields, etc. that might skew the whole "distinct" thing I was looking for versus the t.* idea... I got what I needed. thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question