Count Function

[sanjna000]

Hi guys,

I need to count number of similar Visit length for each client in my database.

for example.

client name Visit Date Visit Length(hr)
Brian 18/10/04 0.5
Brian 19/10/04 0.5
Thomas 18/10/04 0.75
Thomas 19/10/04 0.75
Thomas 20/10/04 0.75

The query should calculate as Brian has 2 (0.5 visit length)
Thomas has 3 (0.75 Visit length)

I am very new to SQL and I am struggling to do this. Could anyone help me with this...

Thanks guys
Sanjna

 

[lespaul]

something like this?:

SELECT [Client Name], [Visit Length], Count(*) FROm tblName Group By [Client Name], [Visit Length]

Leslie

 

[Crowley16]

hmm, not 100% sure this will work but you could try:

SELECT DISTINCT COUNT(clientName), Visit Length(hr) FROM ... WHERE ...

--------------------
Procrastinate Now!

 

[sanjna000]

Hi guys,
Thanks for u r support. but it's not working in that manner cause any client can have more than one visit length type. For instance

client name Visit Date Visit Length(hr)
Brian 18/10/04 0.5
Brian 19/10/04 0.5
Brian 18/10/04 0.75
Thomas 19/10/04 0.75
Thomas 20/10/04 0.75

so i have to count 0.5 visit length count for brian as 2
0.75 visit length count for brian as 1
0.5 visit length count for Thomas as 2

Any idea how to implement this?
Thank you so much guys for all u r support
Sanjna

 

[lespaul]

I'm pretty sure the query I posted does exactly what you asked for. What results do you get from my query?

It should return (from your first example)

Brian .5 2
Thomas .75 3

or (from your second example)

Brian .5 2
Brian .75 1
Thomas .75 2

Leslie

 

[sanjna000]

Hi lespaul,

When i tried u r query, it returns 1 for each client. I couldn't figure out what was wrong. my actual code is as follows:

SELECT Client.[Client Forename], Client.[Client Surname], Client.[Client Addr Ln1], ClientWklyVisit.Date, ClientWklyVisit.[From Time], ClientWklyVisit.[To Time], ClientWklyVisit.[Visit Length], ClientWklyVisit.[Invoice To Account], (Val(Left([Visit Length],2)))+(Val((Right([Visit Length],2))/60)) AS Exp1, Count(*)
FROM Client INNER JOIN ClientWklyVisit ON Client.[Client ID] = ClientWklyVisit.[Client ID]
GROUP BY Client.[Client Forename], Client.[Client Surname], Client.[Client Addr Ln1], ClientWklyVisit.Date, ClientWklyVisit.[From Time], ClientWklyVisit.[To Time], ClientWklyVisit.[Visit Length], ClientWklyVisit.[Invoice To Account], (Val(Left([Visit Length],2)))+(Val((Right([Visit Length],2))/60))
HAVING (((ClientWklyVisit.Date)>=#8/13/2004# And (ClientWklyVisit.Date)<=#8/19/2004#) AND ((ClientWklyVisit.[Invoice To Account]) Like 'LBS*'));

Hope you will be able to help me out with this.

Many Thanks,
Sanjna...

 

[sanjna000]

Hi Lespaul,

I wrote a new query by getting only the ClientWklyVisit table. It did work well as u said. Thank you so much for u r help. I really appriciate it a lot.

Many Thanks,
Sanjna...

 

[almoes]

Hi!

I am trying out something similar, need to retrieve distinctly a field from a table and count the occurences, but I would like to perform the same with another field and merge the result together. So for example if the table have this fields:

City1 City2

NY Barcelona
Paris Zürich
Zürich NY

I would like to retrieve:

NY 2
Paris 1
Zürich 2
Barcelona 1

I tried with the following query:

(Select City1, count(*) from table) UNION (Select City2, count(*))

but then the answer is not merged. How could I do it? Thanxs!

cheers,
alej

 

[PHV]

SELECT City, Sum(CountOf) FROM
(Select City1 As City, count(*) As CountOf from table
UNION Select City2, count(*))


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[almoes]

no need to use 'group by'?

 

[PHV]

Sorry for the typo:
SELECT City, Sum(CountOf) FROM
(Select City1 As City, count(*) As CountOf from table
UNION Select City2, count(*))
GROUP BY City;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[almoes]

Uppps i get an error...it says that 'try to execute a query that does not include 'City' as an aggregate function!

 

[almoes]

ok, with this statement:

(Select City1 as City, count(*) as CountOf from testTable group by City1) UNION ALL (Select City2, count(*) from testTable group by City2)

I get:

City CountOf

NY 1
Paris 1
Zürich 1
Barcelona 1
Zürich 1
NY 1

and now I need to merge the repeted ones... but if i try

Select City, Sum(CountOf) from (clause before)

I get 'error in JOIN' :-(

 

[JonFer]

Probably your parenthese. Try this:

SELECT City, Sum(CountOf) FROM
(Select City1 As City, count(*) As CountOf
from table
group by city1
UNION Select City2, count(*)
from table
group by city2)
GROUP BY City;

John

http://www.interautomateconsulting.com

 

[almoes]

I got it!

Select City, Sum(CountOf) from (Select City1 as City, count(*) as CountOf from testTable group by City1 UNION ALL Select City2, count(*) from testTable group by City2) group by City

the UNION did not like too many parenthesis :-(

thanxs!

 

[almoes]

threads crossed! but thanxs

 

[PHV]

Have you tried this ?
SELECT City, Sum(CountOf) FROM
(Select City1 As City, count(*) As CountOf from table GROUP BY City1
UNION Select City2, count(*) GROUP BY City2)
GROUP BY City;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244