Help needed returning multiple COUNT fields in SQL

Ok I have a table called Table1 which has the follwing fields

Repair_ID (INT),
contact_status(INT)

Now there may be multiple records with the same Repair_ID value. What I need is to be able to return the DISTINCT counts of this field and then have a second column that returns how many records made up that Distinct COUNT. Ie, one column returns a value based on a distinct COUNT the other then returns how many records shared that distinct value

So if one repair ID appears 5 times I need a column with the value 5 and another with the value one

The following query does the first bit - ie returns a single list of distinct repair_id counts, but I cant figure out how to do the second bit

SELECT DISTINCT COUNT(repair_ID) FROM Table1 WHERE contact_status  = 1 GROUP BY repair_ID

Please help
LVL 2
digital_soulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gh081410Commented:
SELECT DISTINCT COUNT(repair_ID),1 as 'Someting' FROM Table1 WHERE contact_status  = 1 GROUP BY repair_ID
I think that's what you want
0
digital_soulAuthor Commented:
gh081410

All that does is returns exactly the same as my query but with an additional column that always has 1 in.
0
gh081410Commented:
I dont understand the question, what do you want in the second collumn?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

digital_soulAuthor Commented:
ok, imagine my dataset consists of the following

Repair ID   contact_status  
1              0
1              1
2              1
1              1
2              1
3              1
3              1
3              1
4              1


I need to output the following

Expr1      Expr2
1            1
2            2
3            2

Basically the first column returns the count of the distinct repair_ids of all records where contact_status = 1 (ie there is only 1 instance of repair_ID 4 so 1 becomes a value, there are 2 instances of repair_ID 1 so 2 becomes a value etc)

The second column then displays the COUNT of all records that fall into the group of the first column. (ie there is only 1 repair_ID that appears once in the query, but there are 2 repair_IDs that appear 3 times in the query)
0
gh081410Commented:
show me exemple of you data and exemple of the querry result you want
0
jerryb30Commented:
Do you want:

Select Repair_ID, sum(contact_status) as contact_Count group by repair_id
?
0
gh081410Commented:
i dont understand this
Expr1      Expr2
1            1
2            2
3            2

do you meen
Repair ID Expr1      Expr2
 1                1            1
 2                2            2
 3               3            2
4                ?             ?

0
gh081410Commented:
I need the real result you want, all collumn and real result base on the values you gave me before
0
digital_soulAuthor Commented:
no. Hmmm, how can I explain this better. Ok the table actually represents customers that have had SMS communication. Some have been contacted once, others have been contacted as many as 5 times. Each customer has a unique repair_ID. Essentially I need to return the number of customers who have had 1 SMS only, the number of customers who have had 5 SMS and everything in between. So the first column would end up looking like this:

1
2
3
4
5

and the second column would then contain the number of distinct repair_IDs that have been contacted that many times where each record in the database represents one instance of contact.
0
digital_soulAuthor Commented:
So if there are 50 instances of distinct repair_ids that have 4 records in the table, then that row would look like

Expr1    Expr2
4          50

And if there was 30 instances of distinct repair_ids that had one record only in the table then that row would look like:

Expr 1    Expr2
1           30

Eventually returning something like

Expr1     Expr2
1           30
2           78
3           123
4           50
5           13
0
gh081410Commented:
SELECT table1.repair_ID, Count(table1.repair_ID) AS Expr1, Sum(IIf([contact_status]=1,1,0)) AS Expr2
FROM table1
GROUP BY table1.repair_ID;
0
gh081410Commented:
instead of Sum(IIf([contact_status]=1,1,0))
you can put a parameter like this

PARAMETERS pContact_Status Short;
SELECT table1.repair_ID, Count(table1.repair_ID) AS Expr1, Sum(IIf([contact_status]=[pContact_Status],1,0)) AS Expr2
FROM table1
GROUP BY table1.repair_ID

is that what you want????
0
digital_soulAuthor Commented:
ok, one final thing. contact_status doesnt actually exist, I just used it to make the query simpler to explain. The actual conditions need to be
WHERE ownerContact.contact_type LIKE '%SMS%' AND ownerContact.contact_status LIKE '%SMS sent%'

Thanks for your help so far, hope you can fit this into your query and it works.

Cheers
0
digital_soulAuthor Commented:
Sorry, OwnerContact being table1
0
gh081410Commented:
Is the last SQL(the simple one) in give you works (The one with you data exemple)?

just put your where condition and i tink it will work
SELECT table1.repair_ID, Count(table1.repair_ID) AS Expr1, Sum(IIf([contact_status]=1,1,0)) AS Expr2
FROM table1
WHERE ownerContact.contact_type LIKE '%SMS%' AND ownerContact.contact_status LIKE '%SMS sent%'
GROUP BY table1.repair_ID;

someting like this with the good names!

0
gh081410Commented:
If the last one, did not help you, I will stop, because I dont want to lose your time.
Probebly someone else will be better then me to help you.
I tink, it's only because I don't understand correctly what you want.
Have a good day!!
0
digital_soulAuthor Commented:
Ok, here is the actual output from a test database im running

The following query:

select contact_ID, repair_ID from OwnerContact WHERE ownerContact.contact_type LIKE '%SMS%' AND ownerContact.contact_status LIKE '%SMS sent%'

Outputs

contact_ID  repair_ID
1621          249  
1624          251  
1625          251  
1627          251  
1638          250  
1642          251  
1644          251  
1645          249  
1648          252  
1651          253  
1653          253  

From this data my query needs to output this:

TimesContacted        NumofCustomersContactedThisMayTimes
1                             2
2                             2
3                             0
4                             0
5                             1
0
digital_soulAuthor Commented:
Can you see the relationship between the actual data and the query I need to output?
0
gh081410Commented:
Now i understand, give me some minutes
0
gh081410Commented:
Is 1 = the costumer was contacted?
And 0 = not contacted
0
digital_soulAuthor Commented:
If you are referring to my original value of 'contact_status' then forget about that.

If you are refering to this:

TimesContacted        NumofCustomersContactedThisMayTimes
1                             2
2                             2
3                             0
4                             0
5                             1


then no. 1 means 1 customer has been contacted 5 times, 0 means 0 customers have been contacted 3 or 4 times
0
gh081410Commented:
you need 2 querry, try this with you own thing

querry 1
SELECT table1.repair_ID, Sum(IIf([contact_status]=1,1,0)) AS NumberOfContact, Count(table1.repair_ID) AS NumberOfSMS
FROM table1
WHERE Condition
GROUP BY table1.repair_ID;


querry 2

SELECT Query1.NumberOfContact AS TimesContacted, Count(Query1.repair_ID) AS NumofCustomersContactedThisMayTimes
FROM Query1
GROUP BY Query1.NumberOfContact;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gh081410Commented:
the problem with this is you wont see timecontacted if it the number of custumer equal 0
If you realy need that, it's possible, just ask me!
0
digital_soulAuthor Commented:
regarding

querry 1
SELECT table1.repair_ID, Sum(IIf([contact_status]=1,1,0)) AS NumberOfContact, Count(table1.repair_ID) AS NumberOfSMS
FROM table1
WHERE Condition
GROUP BY table1.repair_ID;

contact_status does not exist in the table

Well thanks for the help, Is there really no way of doing this in one query then as Im unable to create a query in Access as its a web application
0
gh081410Commented:
I dont think so. But if you do web application, you should think about SQL sever express(It's free to use)
if you do a record count on the fist querry with the criteria of the timecontacted in numberOfContact feild
you will have what you want

like this:
SELECT table1.repair_ID, Sum(IIf([contact_status]=1,1,0)) AS NumberOfContact, Count(table1.repair_ID) AS NumberOfSMS
FROM table1
GROUP BY table1.repair_ID
HAVING (((Sum(IIf([contact_status]=1,1,0)))=2))   '=2 meens the number of timecontacted

you can do a loop of this querry for every timecontacted you want

I,m I understandable???
0
digital_soulAuthor Commented:
Well Im not sure why your query still has any refernece to contact_status because as I stated it does not exist. The only conditions are ownerContact.contact_type LIKE '%SMS%' AND ownerContact.contact_status LIKE '%SMS sent%'

cheers
0
gh081410Commented:
because of your first exemple.
I tough you can understand with this exemple
but rapidly (i,m done for the day)
remove "AND ownerContact.contact_status LIKE '%SMS sent%'" from your where condition

instead of "Sum(IIf([contact_status]=1,1,0))"
try this
"Sum(IIf([contact_status] LIKE '%SMS sent%',1,0))"



0
gh081410Commented:
by the way perhaps it's ok for web project but in Access your suppose to use '*' as wildcard, tell me about that if it work with '%'
IS : LIKE '%SMS sent%'
S/B: LIKE '*SMS sent*'

'%' is for SQL server or Oracle etc..
0
harfangCommented:
Hello,

I read through this thread, and I believe the original question -- how to count count instances -- has not been answered. This needs two queries, but one can be embedded in the other. I tested this on Northwind's Orders table:

    SELECT NbOrders, Count(*) As NbInstances
    FROM (
        SELECT CustomerID, Count(*) AS NbOrders
        FROM Orders
        GROUP BY CustomerID
    )
    GROUP BY NbOrders;

This shows order counts per client, and then the number of clients that had that many orders, which is what you requested, I think. Using your names, it would be:

    SELECT NbCalls, Count(*) As NbCustomers
    FROM (
        SELECT repair_ID, Count(*) AS NbCalls
        FROM Table1
        WHERE contact_status
        GROUP BYrepair_ID
    )
    GROUP BY NbCalls;

Note: in the above all records with non-Null and non-zero contact_status are counted, you can insert any other meaningful selection criteria right there.

Hope this helps,
(°v°)
0
harfangCommented:
Oh, I read once more, and in fact, gh081410's approach {http:#16401548} is almost the same. Only the embedding is missing...
Sorry, gh081410!
(°v°)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.