Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help needed returning multiple COUNT fields in SQL

Posted on 2006-04-07
32
Medium Priority
?
342 Views
Last Modified: 2008-03-17
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
0
Comment
Question by:digital_soul
  • 16
  • 11
  • 2
  • +1
30 Comments
 
LVL 3

Expert Comment

by:gh081410
ID: 16400635
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16400700
gh081410

All that does is returns exactly the same as my query but with an additional column that always has 1 in.
0
 
LVL 3

Expert Comment

by:gh081410
ID: 16400817
I dont understand the question, what do you want in the second collumn?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 2

Author Comment

by:digital_soul
ID: 16400920
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16400924
show me exemple of you data and exemple of the querry result you want
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 16400988
Do you want:

Select Repair_ID, sum(contact_status) as contact_Count group by repair_id
?
0
 
LVL 3

Expert Comment

by:gh081410
ID: 16401006
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401052
I need the real result you want, all collumn and real result base on the values you gave me before
0
 
LVL 2

Author Comment

by:digital_soul
ID: 16401060
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401089
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401106
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401134
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401148
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401162
Sorry, OwnerContact being table1
0
 
LVL 3

Expert Comment

by:gh081410
ID: 16401230
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401258
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401262
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401290
Can you see the relationship between the actual data and the query I need to output?
0
 
LVL 3

Expert Comment

by:gh081410
ID: 16401404
Now i understand, give me some minutes
0
 
LVL 3

Expert Comment

by:gh081410
ID: 16401424
Is 1 = the costumer was contacted?
And 0 = not contacted
0
 
LVL 2

Author Comment

by:digital_soul
ID: 16401459
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
 
LVL 3

Accepted Solution

by:
gh081410 earned 200 total points
ID: 16401548
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401563
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16401587
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16401853
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
 
LVL 2

Author Comment

by:digital_soul
ID: 16402152
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16403843
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
 
LVL 3

Expert Comment

by:gh081410
ID: 16404726
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
 
LVL 58

Assisted Solution

by:harfang
harfang earned 200 total points
ID: 16406739
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
 
LVL 58

Expert Comment

by:harfang
ID: 16406746
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 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