Counting total number of items in each category

Hello everyone,

I have 2 tables. One with a list of countries and another with a list of users. The users table stores the Country they are from as well as other info.

Eg the structure is a little bit like this...

Countries:

--

CountryId

CountryName

Users:

UserId

UserName

CountryId

So, the question is how to a list all my countries with total users in each. Eg, so the results are something like this......

CountryName TotalUsers

United Kingdom 334

United States 1212

France 433

Spain 0

Any help woulld be great as I have been fumbling with this all morning. Im not 100% with SQL yet!!

Cheer

Stephen

here You go...

Code Snippet

Create Table #countries (

[CountryId] int ,

[CountryName] Varchar(100)

);

Insert Into #countries Values('1','USA');

Insert Into #countries Values('2','UK');

Insert Into #countries Values('3','IN');

Create Table #users (

[UserId] int ,

[UserName] Varchar(100) ,

[CountryId] int

);

Insert Into #users Values('1','John','1');

Insert Into #users Values('2','Dale','1');

Insert Into #users Values('3','Thome','2');

--With ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

left Outer Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

--Without ZERO COUNT

Select

[CountryName],

Count([UserId])

from

#countries C

Inner Join #users U on C.[CountryId] = U.[CountryId]

Group By

[CountryName]

|||

Super! Many thanks and thank you for replying so quickly.

Makes sense now - easier than what I was trying to do!!!

Counting things with SQL

I have a database with a table of basket names and a table of items in those
baskets.
I would like to find the size of the basket with the most items in.
I cannot figure out how to do this is T-SQL:-
nMax=0
for each basketName in basket
n=count(records in basketItem where
basket.basketName=basketItem.basketName)
if n>nMax then n=nMax
next
Is there a simple way of doing it, or would it be more efficient to retrieve
the size of each basket into an array in vb.net and figure out the largest
one there?
Andrew
Here is an example for the Northwind database, can be easely compared to
your database with the right key associated.
Select O.OrderID,Count(*) from Orders O
Inner join [Order Details] OD
On O.OrderID = OD.OrderID
Group by O.OrderIDa
Count All Orders Group by the OrderID
(Yeah newsgroupreaders, i know you can easely only count those OrderDetails,
but in this case the poster has the basketname in the 1 "Orders" Table of
the 1:n relationship)
HTH, Jens Smeyer
http://www.sqlserver2005.de
"Andrew Morton" <akm@.in-press.co.uk.invalid> schrieb im Newsbeitrag
news:uOMv$VcQFHA.3356@.TK2MSFTNGP12.phx.gbl...
>I have a database with a table of basket names and a table of items in
>those baskets.
> I would like to find the size of the basket with the most items in.
> I cannot figure out how to do this is T-SQL:-
> nMax=0
> for each basketName in basket
> n=count(records in basketItem where
> basket.basketName=basketItem.basketName)
> if n>nMax then n=nMax
> next
> Is there a simple way of doing it, or would it be more efficient to
> retrieve the size of each basket into an array in vb.net and figure out
> the largest one there?
> Andrew
>
|||"Jens Smeyer" wrote
> Here is an example for the Northwind database, can be easely compared to
> your database with the right key associated.
> Select O.OrderID,Count(*) from Orders O
> Inner join [Order Details] OD
> On O.OrderID = OD.OrderID
> Group by O.OrderIDa
Thanks for that - is the next line meant to be included in the script
somehow?
Is it intended to modify the above query to return only the largest value?

> Count All Orders Group by the OrderID
[Also, is the Northwind database available to those of us who only have MSDE
to experiment with? So many Microsoft examples use it, but without it it can
be hard to follow the examples.]
Andrew
|||[vbcol=seagreen]
///
No this line was just for explaination.

> [Also, is the Northwind database available to those of us who only have
> MSDE to experiment with? So many Microsoft examples use it, but without it
> it can be hard to follow the examples.]
Of course, look here:
http://www.microsoft.com/downloads/d...displaylang=en
HTH, Jens Smeyer.
http://www.sqlserver2005.de
|||hi Andrew,
Andrew Morton wrote:[vbcol=seagreen]
> "Jens Smeyer" wrote
> Thanks for that - is the next line meant to be included in the script
> somehow?
> Is it intended to modify the above query to return only the largest
> value?
nope, this is a short description of what the query is performing..

> [Also, is the Northwind database available to those of us who only
> have MSDE to experiment with? So many Microsoft examples use it, but
> without it it can be hard to follow the examples.]
http://www.microsoft.com/downloads/d...DisplayLang=en
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||>>> Count All Orders Group by the OrderID
> ///
> No this line was just for explaination.
Jens and Andrea,
That makes sense now - I was looking in the help at ALL and wondering how
that connected with the query, and "the" didn't get coloured when I typed it
into the stored procedure editor in VS.NET so I knew I was going wrong
somewhere!
And thank you for the link to the sample databases download. I couldn't find
it by searching just because of the sheer number of items found.
Best regards,
Andrew

Counting rows in a report

i have a report with 1 group and items under the group.

i want to add a new field called Sl. no. at the group level which keeps the count on groups...by that i mean say if there are 10 groups i want the number from 1 to 10 appear against each group as
1 Grp1
2 Grp2
3 Grp3
.
.
.
10 Grp10

How can i achieve the above. I tried the rownumber but it returns the number of rows the group has. The levels i have is "table1" the main scope and "table1_Group1" the group scope which in the table1 scope.

Any help will be appreciated.

what you are doing is similiar to what I wanted to do to get a generic counter... someone else had posted the below as an answer for me. This should work for you as well.

Put this in your code window:

Dim numGroupRow as Double

Function GetRowNumber() as Double
numGroupRow += 1
Return numGroupRow
End Function

Then put this in your group row textbox

= Code.GetRowNumber()

This will increment every group row. If you need to start over at some point the code will need to change.

|||thanks for ur reply.
It works as desired but i have some sorting defined on a field and i want to this sl.no field to move with the sort.

I will try to alter the code but if the solution is out there i would love to have it.

Thaks
|||

I don't think sorting should matter as this is a generic counter and is only incremented by 1 when you call it.

Since the sort acts before the fields are displayed, it shouldn't matter.

|||I thought so too but it is not sorting with the other fields. This is what happens the initial report comes as:

1....valA.......valB
2....valC.......valD
.
.
.
.
40...valE......valF
41...valG.....valH

now when i sort on first field the output comes as

1...valE......valF
2....valC.......valD
3....valA.......valB
.
.
.
.

41...valG.....valH

instead of
40...valE......valF
2....valC.......valD
1....valA.......valB
.
.
.
.

41...valG.....valH
|||

ah, I see.

That is becuase the counter is working as expected.

you will probobaly have to create a field on the dataset your self, add a new field and refernce the code in your field.

then place that field on your report and try the sort that way. You may have to play around with some grouping.

Counting items selected in MVP

Hey All,
Is there a way to find out the number of items selected in a multi-value
paramter?
I would like to know if the user has selected 1, 2,...n values for a single
parameter.
Thanks in advance,
Michael CTry:
=Parameters!MV1.Count|||Would something like this work for out:
Parameters!my_param.Value.Split(",").Length
> Hey All,
> Is there a way to find out the number of items selected in a
> multi-value
> paramter?
> I would like to know if the user has selected 1, 2,...n values for a
> single
> parameter.
> Thanks in advance,
> Michael C
>

Counting Items in Categories

Ive got this monster which will give me a parent categoryName and the number of records linked to a child of that category, I want to use it for a directory where the list of categories has the number of records in brackets next to them. Note: a A listing will show up in each category count it is associated with

Like

Accommodation (10)
Real Estate(30)
Automotive(2)
Education(1)...

Select trade_category.iCategory_Name,Listing_category.iPa rentID,count(Listing_category.iCategoryID) as num
from Listing_category,trade_category Where Listing_category.iParentID = trade_category.iCategoryID Group by
Listing_category.iParentID,trade_category.iCategor y_Name
Union ALL
Select Freecategory.sName,Listing_category.iParentID,coun t(Listing_category.iCategoryID) as num
from Listing_category,Freecategory Where Listing_category.iParentID = Freecategory.iFreeID Group by
Listing_category.iParentID,Freecategory.sName

Which Produces

Real Estate 12401 12
Extreme Sports 3 4

I would Like to get the same query to produce a list of all the empty records too.
so
ID Count
Accommodation 6112 0
Real Estate 12401 12
retail 12402 0
Extreme Sports 3 4
Cycling 5 0There is no such concept of an 'empty record'. If you were to describe to me an 'empty record', what would it be? In situations similar to what you describe, a record can have one of the following characteristics:

- Contain null values for one or more of its fields
- Not be returned with respect to some given criteria.

But there is no mention of an 'empty record' in relational theory or in any Database implementation. Looking at your query, I can't think of what it is you're actually trying to achieve, except in the case where a parent category may have no children, you need to return a result set similar to the following:

{ParentID, ChildCount}
ParentA, 0

When viewed in this way, the problem becomes a trivial LEFT JOIN query that will return all rows from set A irrespective of the contents of set B. In your example however, instead of returning the rows from Set B you will just return a count of the rows.

Select
SetA.columnA,
count(SetB.columnA)
from
SetA

left outer join SetB
on SetA.ColumnA = SetB.ColumnB

Remember: Simplification should be the goal of every developer. A paraphrased quote I once heard said: Perfection is reached not when you can no longer add to it, but when you can no longer take anything away.|||Ive found a short term solution will look at speeding it up when I have some spare time, and I have the live version working so it makes a bit more sense.

Start of December

Counting items and returning values

I have a table of product orders. It contains a row for "platform" and I
need to return how many times each platform is listed in the DB

Example data for platform could be:
XBOX
XBOX
XBOX
PLAYSTATION
PLAYSTATION
GAMECUBE
PLAYSTATION

I'd like the data to be returned as

XBOX - 3
PLAYSTATION - 3
GAMECUBE - 1

How would I go about doing this please?if the name of the column is "name":
SELECT name, COUNT(name)
GROUP BY name

HTH,
-Cliff

"Andrew Banks" <banksy@.nojunkblueyonder.co.uk> wrote in message
news:ks%ac.351$lN4.6788392@.news-text.cableinet.net...
> I have a table of product orders. It contains a row for "platform" and I
> need to return how many times each platform is listed in the DB
> Example data for platform could be:
> XBOX
> XBOX
> XBOX
> PLAYSTATION
> PLAYSTATION
> GAMECUBE
> PLAYSTATION
> I'd like the data to be returned as
> XBOX - 3
> PLAYSTATION - 3
> GAMECUBE - 1
> How would I go about doing this please?

Counting items

I want to run a select query and also using that items keys get the count of
the items in the inventory db, kinda like this below but does not Parse
SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
dbo.Manufact_Company.ManuComp_ID, XCount AS
(SELECT Count(ID)
FROM wholesaleinv
WHERE Pattern_ID = dbo.Pattern.Pattern_ID
AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
FROM dbo.Pattern INNER JOIN
dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
dbo.PTC2CLEAN.Pattern_ID INNER JOIN
dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
dbo.ProductType.ProdType_ID INNER JOIN
dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
dbo.Manufact_Company.ManuComp_IDPlease post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"TdarTdar" <TdarTdar@.discussions.microsoft.com> wrote in message
news:231CE73E-0716-46C4-A4C4-B7C688023022@.microsoft.com...
>I want to run a select query and also using that items keys get the count
>of
> the items in the inventory db, kinda like this below but does not Parse
> SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID,
> dbo.Manufact_Company.ManuComp_ID, XCount AS
> (SELECT Count(ID)
> FROM wholesaleinv
> WHERE Pattern_ID = dbo.Pattern.Pattern_ID
> AND ManuComp_ID = dbo.Manufact_Company.ManuComp_ID AND
> Prodtype_ID = dbo.PTC2CLEAN.ProdType_ID)
> FROM dbo.Pattern INNER JOIN
> dbo.PTC2CLEAN ON dbo.Pattern.Pattern_ID =
> dbo.PTC2CLEAN.Pattern_ID INNER JOIN
> dbo.ProductType ON dbo.PTC2CLEAN.ProdType_ID =
> dbo.ProductType.ProdType_ID INNER JOIN
> dbo.Manufact_Company ON dbo.PTC2CLEAN.ManuComp_ID =
> dbo.Manufact_Company.ManuComp_ID|||A quick glance reveals a peculiarity - "..., XCount AS (select..." is wrong.
Replace it with "(select ...) as XCount".
Any other help will be available if you share your DDL with us.
ML

Counting items

Hi,

I'm trying to include the COUNT(*) value of a sub-query in the results of a parent query. My SQL code is:

SELECT appt.ref, (Case When noteCount > 0 Then 1 Else 0 End) AS notes FROM touchAppointments appt, (SELECT COUNT(*) as noteCount FROM touchNotes WHERE appointment=touchAppointments.ref) note WHERE appt.practitioner=1

This comes up with an error basically saying that 'touchAppointments' isn't valid in the subquery. How can I get this statement to work and return the number of notes that relate to the relevant appointment?

Cheers.Hi!

Would this one help out?

SELECT appt.ref
, (Case When noteCount > 0 Then 1 Else 0 End) AS notes
FROM touchAppointments appt
, (SELECT appointment
, COUNT(*) as noteCount
FROM touchNotes) note
WHERE appt.practitioner=1
AND appt.ref = note.appointment

Greetings,
Carsten|||I would have writen like this
<code>
SELECT appt.ref , count(*)/count(*) AS notes
FROM touchAppointments as appt inner join touchNotes as note
on appt.ref = note.appointment
WHERE appt.practitioner=1 group by appt.ref
</code>|||You would?

count(*)/count(*) is at best going to return only 1s or Nulls, and at worst would return DivZero errors.

There are serveral ways to do this. CarstenK had one, though it is preferable to use a JOIN rather than linking tables in the WHERE clause.

Here are two more methods:

SELECT touchAppointments.ref, cast(count(touchNotes.appointment) as bit) notes
FROM touchAppointments
left outer join touchNotes on touchNotes.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1
GROUP BY touchAppointments.ref

SELECT touchAppointments.ref, isnull(notesSubquery.hasnotes, 0) as notes
FROM touchAppointments
left outer join (select distinct touchNotes.appointment, 1 as hasnotes from touchNotes) notesSubquery
on notesSubquery.appointment = touchAppointments.ref
WHERE touchApointment.practictioner = 1|||you are right
count(*)/count(*) is at best going to return only 1s
but how come nulls and div by zero error(even at worst case) with inner join on touchAppointments.ref.|||How do you get the "0" paulbrooks wants to get with his CASE (....)?

Carsten|||Blindman,

Your second solution worked the trick. It returns a 1 for true and 0 for false, which is exactly what I needed.

Thanks a lot, guys.

Paul

Counting Group fields

Is there a way I can count the number of grouped items in a textbox in a
report?
When I try to use =count(Fields!UserID.Value), the report gives me the
amount of records in the query.
I also tried using count distinct but that returned the same results.
Is there anyway I can just count the grouped listings? Or maybe count the
number of table rows?
TIA,
JacksonI think you can specify a scope for the count method ie
=count(Fields!UserID.Value, "GroupName")
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp|||Thanks Thomo,
Where would I be able to find the group name? The only group names I've
found is called table1_UserID and table1_details_group but they don't seem
to work.
Thanks again for the help,
Jackson
"Thomo" <greg.thomson@.gmail.com> wrote in message
news:1147102016.645069.295180@.e56g2000cwe.googlegroups.com...
>I think you can specify a scope for the count method ie
> =count(Fields!UserID.Value, "GroupName")
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_9bp0.asp
>|||Those sound like legit group names. You can find group names by right
clicking on the beginning of a group row and selecting edit group from
the context menu. The name will be on the top portion of the general
tab of the Grouping and Sorting Properties window. The names work fine
as the scope argument for the count statement, making sure that the
group name is in double quotes since it needs to be a string argument.
You didn't say what kind of error you're getting, but maybe its the
placement of your aggregate field. Try putting it in the group footer,
for example, and see if it works.|||Putting it into the group footer worked. I was trying it in the Page
footer. But the problem is that when I do a count it counts all of the
records.
I just want to include a count of how many Users are included in the report.
In my report I have UserID's grouped and within the group is the details of
their previous transactions. And for some reason it's counting the records
instead of just the groups.
Any suggestions?
Thanks again,
Jackson
"dba56" <louvig4@.att.net> wrote in message
news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
> Those sound like legit group names. You can find group names by right
> clicking on the beginning of a group row and selecting edit group from
> the context menu. The name will be on the top portion of the general
> tab of the Grouping and Sorting Properties window. The names work fine
> as the scope argument for the count statement, making sure that the
> group name is in double quotes since it needs to be a string argument.
> You didn't say what kind of error you're getting, but maybe its the
> placement of your aggregate field. Try putting it in the group footer,
> for example, and see if it works.
>|||doh, nevermind. I took a look at the link that Thomo had sent and I tried
using countdistinct again in the footer and it worked.
I could have sworn I tried countdistinct and it came out different.
Oh well thanks for all of the help.
Jackson
"Jackson" <jackson_num5@.yahoo.com> wrote in message
news:eK%23I1ntcGHA.3632@.TK2MSFTNGP02.phx.gbl...
> Putting it into the group footer worked. I was trying it in the Page
> footer. But the problem is that when I do a count it counts all of the
> records.
> I just want to include a count of how many Users are included in the
> report.
> In my report I have UserID's grouped and within the group is the details
> of their previous transactions. And for some reason it's counting the
> records instead of just the groups.
> Any suggestions?
> Thanks again,
> Jackson
> "dba56" <louvig4@.att.net> wrote in message
> news:1147115725.622812.317540@.y43g2000cwc.googlegroups.com...
>> Those sound like legit group names. You can find group names by right
>> clicking on the beginning of a group row and selecting edit group from
>> the context menu. The name will be on the top portion of the general
>> tab of the Grouping and Sorting Properties window. The names work fine
>> as the scope argument for the count statement, making sure that the
>> group name is in double quotes since it needs to be a string argument.
>> You didn't say what kind of error you're getting, but maybe its the
>> placement of your aggregate field. Try putting it in the group footer,
>> for example, and see if it works.
>

Counting filtered fields

I have a field for the number of items. I need to count how many of these items contain the word "hello" and I need to express this as a percentage of the total number of items. What would be the best way to do this?
Thankswhileprintingrecords;
numbervar a;
if field='hello' then
a:=a+1

i haven't tested it, give it try...

good luck