Using SS2000, VS2003, RS2000.
I want to list all of the individual orders and how they were shipped. Then
I want to count the total number of orders and get a count for each method of
shipment. So, the data might look like this:
Order # Shipped
1 UPS
2 FedEx
3 USPS
4 FedEx
5 UPS
6 FedEx
Total Orders 6
FedEx 3
UPS 2
USPS 1
I tried adding a group and putting a filter in the group (FedEx) but it only
limited the detail part of the report to the rows that were shipped by FedEx.
Now I can't get rid of that filter. I even deleted all of the headers,
footers, groups and detail section and it still only gives me the rows with
FedEx.
Any ideas,
Thanks,
--
Dan D.I'm closer but not there yet. What I have now is this:
Order # Shipped
2 FedEx
4 FedEx
6 FedEx
Total Orders 3
FedEx 3
1 UPS
5 UPS
Total Orders 2
UPS 2
3 USPS
Total Orders 1
USPS 1
But what I need is what was in my original post
--
Dan D.
"Dan D." wrote:
> Using SS2000, VS2003, RS2000.
> I want to list all of the individual orders and how they were shipped. Then
> I want to count the total number of orders and get a count for each method of
> shipment. So, the data might look like this:
> Order # Shipped
> 1 UPS
> 2 FedEx
> 3 USPS
> 4 FedEx
> 5 UPS
> 6 FedEx
> Total Orders 6
> FedEx 3
> UPS 2
> USPS 1
> I tried adding a group and putting a filter in the group (FedEx) but it only
> limited the detail part of the report to the rows that were shipped by FedEx.
> Now I can't get rid of that filter. I even deleted all of the headers,
> footers, groups and detail section and it still only gives me the rows with
> FedEx.
> Any ideas,
> Thanks,
> --
> Dan D.|||Dan,
Can you do this with 2 datasets and 2 tables?
Dataset1:
SELECT orderID, carrier FROM orders ORDER BY orderID
Dataset2:
SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
carrier ORDER BY COUNT(orderID) DESC
You can use a header/footer row for the grand total.
-Josh
Dan D. wrote:
> I'm closer but not there yet. What I have now is this:
> Order # Shipped
> 2 FedEx
> 4 FedEx
> 6 FedEx
> Total Orders 3
> FedEx 3
> 1 UPS
> 5 UPS
> Total Orders 2
> UPS 2
> 3 USPS
> Total Orders 1
> USPS 1
> But what I need is what was in my original post
> --
> Dan D.
>
> "Dan D." wrote:
> > Using SS2000, VS2003, RS2000.
> > I want to list all of the individual orders and how they were shipped. Then
> > I want to count the total number of orders and get a count for each method of
> > shipment. So, the data might look like this:
> >
> > Order # Shipped
> > 1 UPS
> > 2 FedEx
> > 3 USPS
> > 4 FedEx
> > 5 UPS
> > 6 FedEx
> >
> > Total Orders 6
> > FedEx 3
> > UPS 2
> > USPS 1
> >
> > I tried adding a group and putting a filter in the group (FedEx) but it only
> > limited the detail part of the report to the rows that were shipped by FedEx.
> > Now I can't get rid of that filter. I even deleted all of the headers,
> > footers, groups and detail section and it still only gives me the rows with
> > FedEx.
> >
> > Any ideas,
> >
> > Thanks,
> > --
> > Dan D.|||It's worth a try. I'm wondering RS will keep the two datasets in sync. BTW, I
also posted a different example this morning under the subject "is this
possible in RS".
For the time being, I've created a another group on the carrier. Even though
it's not in the format the client wanted, it will give the counts they want.
I'll keep experimenting, though and try your idea.
Thanks,
--
Dan D.
"Josh" wrote:
> Dan,
> Can you do this with 2 datasets and 2 tables?
> Dataset1:
> SELECT orderID, carrier FROM orders ORDER BY orderID
> Dataset2:
> SELECT carrier, COUNT(orderID) AS carrierCount FROM orders GROUP BY
> carrier ORDER BY COUNT(orderID) DESC
> You can use a header/footer row for the grand total.
> -Josh
>
> Dan D. wrote:
> > I'm closer but not there yet. What I have now is this:
> > Order # Shipped
> >
> > 2 FedEx
> > 4 FedEx
> > 6 FedEx
> > Total Orders 3
> > FedEx 3
> >
> > 1 UPS
> > 5 UPS
> > Total Orders 2
> > UPS 2
> >
> > 3 USPS
> > Total Orders 1
> > USPS 1
> >
> > But what I need is what was in my original post
> > --
> > Dan D.
> >
> >
> > "Dan D." wrote:
> >
> > > Using SS2000, VS2003, RS2000.
> > > I want to list all of the individual orders and how they were shipped. Then
> > > I want to count the total number of orders and get a count for each method of
> > > shipment. So, the data might look like this:
> > >
> > > Order # Shipped
> > > 1 UPS
> > > 2 FedEx
> > > 3 USPS
> > > 4 FedEx
> > > 5 UPS
> > > 6 FedEx
> > >
> > > Total Orders 6
> > > FedEx 3
> > > UPS 2
> > > USPS 1
> > >
> > > I tried adding a group and putting a filter in the group (FedEx) but it only
> > > limited the detail part of the report to the rows that were shipped by FedEx.
> > > Now I can't get rid of that filter. I even deleted all of the headers,
> > > footers, groups and detail section and it still only gives me the rows with
> > > FedEx.
> > >
> > > Any ideas,
> > >
> > > Thanks,
> > > --
> > > Dan D.
>
Showing posts with label total. Show all posts
Showing posts with label total. Show all posts
Sunday, February 19, 2012
Counting votes
Hello, all. We have a student election application where we need to
determine:
- Total votes for each candidate in each category
- Total votes for all candiates for that same category
Here are the tables: tblVotes holds the students' votes; tblNominees holds
the candidates for the various offices; tblValidNomineeCategories which
holds the variouses offices they can run for. One person may be running for
more than one office. Some candidates are write-ins, so they won't show up
in the tblNominees; we want to get an aggregate count of them for each
voting category. We've thought of using a cursor to loop through, but there
must a better way.
Here's the DDL of the three tables,
CREATE TABLE [tblVotes] (
[personID] [int] NOT NULL ,
[nomineeID] [int] NOT NULL ,
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeID],
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblNominees] (
[personID] [int] NOT NULL , --> ID of student voter
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
DEFAULT (getdate()),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeCategoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
(
[personID]
) REFERENCES [tblGeneralPerson] (
[personID]
),
CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
(
[nomineeCategoryID]
) REFERENCES [tblValidNomineeCategory] (
[nomineeCategoryID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblValidNomineeCategory] (
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[displayOrder] [smallint] NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT
[DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
(
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's some sample data in each table,
tblVotes:
personID nomineeID nomineeCategoryID isWriteIn dateAdded
----
4 36 STBPR 0
2/25/2005 5:01:00 PM
tblNominees:
personID nomineeCategoryID platformFilename isActive dateAdded
dateLastMod
----
--
36 STBPR platform_2.doc 1
2/4/2005 2/4/2005 11:22:00 AM
tblValidNomineeCategory:
nomineeCategoryID nomineeCategoryDesc displayOrder isActive
dateAdded
----
--
STBPR Student Body President 10 1
1/24/2005 11:38:00 AMI think I've got a start on this,
select a.nomineeid, count(a.nomineeid) [countPer],
(select count(*) from tblvotes c where c.nomineecategoryid = 'STBPR')
[countAll]
from dbo.tblvotes a join dbo.tblnominees b on a.nomineeid = b.personid
where a.nomineecategoryid = 'STBPR'
group by a.nomineeid
This returns,
nomineeID countPer countAll
36 3 4
37 1 4
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:OiXjSURHFHA.2616@.tk2msftngp13.phx.gbl...
> Hello, all. We have a student election application where we need to
> determine:
> - Total votes for each candidate in each category
> - Total votes for all candiates for that same category
> Here are the tables: tblVotes holds the students' votes; tblNominees holds
> the candidates for the various offices; tblValidNomineeCategories which
> holds the variouses offices they can run for. One person may be running
> for more than one office. Some candidates are write-ins, so they won't
> show up in the tblNominees; we want to get an aggregate count of them for
> each voting category. We've thought of using a cursor to loop through, but
> there must a better way.
> Here's the DDL of the three tables,
> CREATE TABLE [tblVotes] (
> [personID] [int] NOT NULL ,
> [nomineeID] [int] NOT NULL ,
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeID],
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblNominees] (
> [personID] [int] NOT NULL , --> ID of student voter
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
> DEFAULT (getdate()),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeCategoryID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
> (
> [personID]
> ) REFERENCES [tblGeneralPerson] (
> [personID]
> ),
> CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
> (
> [nomineeCategoryID]
> ) REFERENCES [tblValidNomineeCategory] (
> [nomineeCategoryID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblValidNomineeCategory] (
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [displayOrder] [smallint] NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
> DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT
> [DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
> CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
> (
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Here's some sample data in each table,
> tblVotes:
> personID nomineeID nomineeCategoryID isWriteIn dateAdded
> ----
> 4 36 STBPR 0 2/25/2005
> 5:01:00 PM
> tblNominees:
> personID nomineeCategoryID platformFilename isActive dateAdded
> dateLastMod
> ----
--
> 36 STBPR platform_2.doc 1
> 2/4/2005 2/4/2005 11:22:00 AM
> tblValidNomineeCategory:
> nomineeCategoryID nomineeCategoryDesc displayOrder isActive
> dateAdded
> ----
--
> STBPR Student Body President 10 1
> 1/24/2005 11:38:00 AM
>|||>>Some candidates are write-ins, so they won't show up in the
Nominees;<<
Please stop using that silly tbl- prefix. But to the point, if you
allow write-ins, then by definition they are a nominee. This is an
attribute of a nominee and not of the ballot.
While I am grateful for the DDL, I think that you over did it a bit.
Never use bits or other asembly language things in SQL, we do not need
the audit trail columns that should be handled with a log tool, pick
clearer names for attributes, etc.
CREATE TABLE Ballots
(voter_student_id INTEGER NOT NULL
REFERENCES Students (student_id),
nominee_student_id INTEGER NOT NULL
REFERENCES Nominees (student_id),
office_id VARCHAR(5) NOT NULL
REFERENCES Offices(office_id),
PRIMARY KEY (student_id, nominee_id, office_id));
CREATE TABLE Nominees
(student_id INTEGER NOT NULL
REFERENCES Students (student_id),
office_id VARCHAR (5) NOT NULL,
platform_file_name VARCHAR (500) NULL,
candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
CHECK (is_writein IN ('W', 'N'))
PRIMARY KEY (nominee_student_id, office_id);
CREATE TABLE Offices
(office_id VARCHAR (5) NOT NULL PRIMARY KEY,
office_desc VARCHAR (95) NOT NULL,
officeholder_student_id INTEGER -- null means vacant
REFERENCES Students (student_id));
SELECT nominee_student_id, office_id, COUNT(*) AS votes
FROM Ballots AS B
GROUP BY nominee_student_id, office_id;
SELECT office_id, COUNT(*) AS votes
FROM Ballots
GROUP BY office_id;
You can combine them into one query:
SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
(SELECT COUNT(*) AS votes
FROM Ballots AS B2
WHERE B1.office_id = B2.office_id) AS total_cast
FROM Ballots AS B1
GROUP BY nominee_student_id, office_id;|||Thank you, Celko. Your guidance is much appreciated, along with the correct
DDL. Thanks :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Celko, I've got one followup question: Why are bit columns not a good idea
in SQL Server tables? Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Finding thats fairly easy task.:)
http://tinyurl.com/42evx
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
> Celko, I've got one followup question: Why are bit columns not a good idea
> in SQL Server tables? Thanks.
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
>|||Thanks, Roji. Very in-depth and informative. Does Celko have his own web
site?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Finding thats fairly easy task.:)
> http://tinyurl.com/42evx
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
>|||Thats more easy :)
http://www.celko.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks, Roji. Very in-depth and informative. Does Celko have his own web
> site?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Roji :)
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23E4xr4aHFHA.3572@.TK2MSFTNGP14.phx.gbl...
> Thats more easy :)
> http://www.celko.com/
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
>
determine:
- Total votes for each candidate in each category
- Total votes for all candiates for that same category
Here are the tables: tblVotes holds the students' votes; tblNominees holds
the candidates for the various offices; tblValidNomineeCategories which
holds the variouses offices they can run for. One person may be running for
more than one office. Some candidates are write-ins, so they won't show up
in the tblNominees; we want to get an aggregate count of them for each
voting category. We've thought of using a cursor to loop through, but there
must a better way.
Here's the DDL of the three tables,
CREATE TABLE [tblVotes] (
[personID] [int] NOT NULL ,
[nomineeID] [int] NOT NULL ,
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeID],
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tblNominees] (
[personID] [int] NOT NULL , --> ID of student voter
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
DEFAULT (getdate()),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
(
[personID],
[nomineeCategoryID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
(
[personID]
) REFERENCES [tblGeneralPerson] (
[personID]
),
CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
(
[nomineeCategoryID]
) REFERENCES [tblValidNomineeCategory] (
[nomineeCategoryID]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblValidNomineeCategory] (
[nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[displayOrder] [smallint] NULL ,
[isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
DEFAULT (1),
[dateAdded] [smalldatetime] NULL CONSTRAINT
[DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
(
[nomineeCategoryID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Here's some sample data in each table,
tblVotes:
personID nomineeID nomineeCategoryID isWriteIn dateAdded
----
4 36 STBPR 0
2/25/2005 5:01:00 PM
tblNominees:
personID nomineeCategoryID platformFilename isActive dateAdded
dateLastMod
----
--
36 STBPR platform_2.doc 1
2/4/2005 2/4/2005 11:22:00 AM
tblValidNomineeCategory:
nomineeCategoryID nomineeCategoryDesc displayOrder isActive
dateAdded
----
--
STBPR Student Body President 10 1
1/24/2005 11:38:00 AMI think I've got a start on this,
select a.nomineeid, count(a.nomineeid) [countPer],
(select count(*) from tblvotes c where c.nomineecategoryid = 'STBPR')
[countAll]
from dbo.tblvotes a join dbo.tblnominees b on a.nomineeid = b.personid
where a.nomineecategoryid = 'STBPR'
group by a.nomineeid
This returns,
nomineeID countPer countAll
36 3 4
37 1 4
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:OiXjSURHFHA.2616@.tk2msftngp13.phx.gbl...
> Hello, all. We have a student election application where we need to
> determine:
> - Total votes for each candidate in each category
> - Total votes for all candiates for that same category
> Here are the tables: tblVotes holds the students' votes; tblNominees holds
> the candidates for the various offices; tblValidNomineeCategories which
> holds the variouses offices they can run for. One person may be running
> for more than one office. Some candidates are write-ins, so they won't
> show up in the tblNominees; we want to get an aggregate count of them for
> each voting category. We've thought of using a cursor to loop through, but
> there must a better way.
> Here's the DDL of the three tables,
> CREATE TABLE [tblVotes] (
> [personID] [int] NOT NULL ,
> [nomineeID] [int] NOT NULL ,
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [isWriteIn] [bit] NULL CONSTRAINT [DF_tblVotes_isWriteIn] DEFAULT (0),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblVote_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblVote] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeID],
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblNominees] (
> [personID] [int] NOT NULL , --> ID of student voter
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [platformFilename] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblNominees_isActive] DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateAdded]
> DEFAULT (getdate()),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblNominees_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblNominees] PRIMARY KEY CLUSTERED
> (
> [personID],
> [nomineeCategoryID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblNominees_tblGeneralPerson] FOREIGN KEY
> (
> [personID]
> ) REFERENCES [tblGeneralPerson] (
> [personID]
> ),
> CONSTRAINT [FK_tblNominees_tblValidNomineeCategory]
FOREIGN KEY
> (
> [nomineeCategoryID]
> ) REFERENCES [tblValidNomineeCategory] (
> [nomineeCategoryID]
> )
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tblValidNomineeCategory] (
> [nomineeCategoryID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [nomineeCategoryDesc] [varchar] (95) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [displayOrder] [smallint] NULL ,
> [isActive] [bit] NULL CONSTRAINT [DF_tblValidNomineeCategory_isActive]
> DEFAULT (1),
> [dateAdded] [smalldatetime] NULL CONSTRAINT
> [DF_tblValidNomineeCategory_dateAdded] DEFAULT (getdate()),
> CONSTRAINT [PK_tblValidNomineeCategory] PRIMARY KEY CLUSTERED
> (
> [nomineeCategoryID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> Here's some sample data in each table,
> tblVotes:
> personID nomineeID nomineeCategoryID isWriteIn dateAdded
> ----
> 4 36 STBPR 0 2/25/2005
> 5:01:00 PM
> tblNominees:
> personID nomineeCategoryID platformFilename isActive dateAdded
> dateLastMod
> ----
--
> 36 STBPR platform_2.doc 1
> 2/4/2005 2/4/2005 11:22:00 AM
> tblValidNomineeCategory:
> nomineeCategoryID nomineeCategoryDesc displayOrder isActive
> dateAdded
> ----
--
> STBPR Student Body President 10 1
> 1/24/2005 11:38:00 AM
>|||>>Some candidates are write-ins, so they won't show up in the
Nominees;<<
Please stop using that silly tbl- prefix. But to the point, if you
allow write-ins, then by definition they are a nominee. This is an
attribute of a nominee and not of the ballot.
While I am grateful for the DDL, I think that you over did it a bit.
Never use bits or other asembly language things in SQL, we do not need
the audit trail columns that should be handled with a log tool, pick
clearer names for attributes, etc.
CREATE TABLE Ballots
(voter_student_id INTEGER NOT NULL
REFERENCES Students (student_id),
nominee_student_id INTEGER NOT NULL
REFERENCES Nominees (student_id),
office_id VARCHAR(5) NOT NULL
REFERENCES Offices(office_id),
PRIMARY KEY (student_id, nominee_id, office_id));
CREATE TABLE Nominees
(student_id INTEGER NOT NULL
REFERENCES Students (student_id),
office_id VARCHAR (5) NOT NULL,
platform_file_name VARCHAR (500) NULL,
candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
CHECK (is_writein IN ('W', 'N'))
PRIMARY KEY (nominee_student_id, office_id);
CREATE TABLE Offices
(office_id VARCHAR (5) NOT NULL PRIMARY KEY,
office_desc VARCHAR (95) NOT NULL,
officeholder_student_id INTEGER -- null means vacant
REFERENCES Students (student_id));
SELECT nominee_student_id, office_id, COUNT(*) AS votes
FROM Ballots AS B
GROUP BY nominee_student_id, office_id;
SELECT office_id, COUNT(*) AS votes
FROM Ballots
GROUP BY office_id;
You can combine them into one query:
SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
(SELECT COUNT(*) AS votes
FROM Ballots AS B2
WHERE B1.office_id = B2.office_id) AS total_cast
FROM Ballots AS B1
GROUP BY nominee_student_id, office_id;|||Thank you, Celko. Your guidance is much appreciated, along with the correct
DDL. Thanks :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Celko, I've got one followup question: Why are bit columns not a good idea
in SQL Server tables? Thanks.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
> Nominees;<<
> Please stop using that silly tbl- prefix. But to the point, if you
> allow write-ins, then by definition they are a nominee. This is an
> attribute of a nominee and not of the ballot.
> While I am grateful for the DDL, I think that you over did it a bit.
> Never use bits or other asembly language things in SQL, we do not need
> the audit trail columns that should be handled with a log tool, pick
> clearer names for attributes, etc.
> CREATE TABLE Ballots
> (voter_student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> nominee_student_id INTEGER NOT NULL
> REFERENCES Nominees (student_id),
> office_id VARCHAR(5) NOT NULL
> REFERENCES Offices(office_id),
> PRIMARY KEY (student_id, nominee_id, office_id));
> CREATE TABLE Nominees
> (student_id INTEGER NOT NULL
> REFERENCES Students (student_id),
> office_id VARCHAR (5) NOT NULL,
> platform_file_name VARCHAR (500) NULL,
> candidancy_type CHAR(1) DEFAULT 'N' NOT NULL -- nominated, write-in
> CHECK (is_writein IN ('W', 'N'))
> PRIMARY KEY (nominee_student_id, office_id);
> CREATE TABLE Offices
> (office_id VARCHAR (5) NOT NULL PRIMARY KEY,
> office_desc VARCHAR (95) NOT NULL,
> officeholder_student_id INTEGER -- null means vacant
> REFERENCES Students (student_id));
>
> SELECT nominee_student_id, office_id, COUNT(*) AS votes
> FROM Ballots AS B
> GROUP BY nominee_student_id, office_id;
>
> SELECT office_id, COUNT(*) AS votes
> FROM Ballots
> GROUP BY office_id;
> You can combine them into one query:
> SELECT B1.nominee_student_id, B1.office_id, COUNT(*) AS votes,
> (SELECT COUNT(*) AS votes
> FROM Ballots AS B2
> WHERE B1.office_id = B2.office_id) AS total_cast
> FROM Ballots AS B1
> GROUP BY nominee_student_id, office_id;
>
>|||Finding thats fairly easy task.:)
http://tinyurl.com/42evx
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
> Celko, I've got one followup question: Why are bit columns not a good idea
> in SQL Server tables? Thanks.
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1109550295.981105.207210@.z14g2000cwz.googlegroups.com...
>|||Thanks, Roji. Very in-depth and informative. Does Celko have his own web
site?
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
> Finding thats fairly easy task.:)
> http://tinyurl.com/42evx
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:ONUN83ZHFHA.3088@.tk2msftngp13.phx.gbl...
>|||Thats more easy :)
http://www.celko.com/
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
> Thanks, Roji. Very in-depth and informative. Does Celko have his own web
> site?
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:%2349mp%23ZHFHA.3332@.TK2MSFTNGP15.phx.gbl...
>|||Thanks, Roji :)
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23E4xr4aHFHA.3572@.TK2MSFTNGP14.phx.gbl...
> Thats more easy :)
> http://www.celko.com/
>
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
> news:eThiasaHFHA.1096@.tk2msftngp13.phx.gbl...
>
counting total rows when using GROUP BY
hi,
i have a stored procedure
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsWHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)GROUP BY UserNameUNIONSELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisitFROM UserVisitsAS UserVisits_1WHERE (ProductID = @.ProductID)AND (UserNameISNULL)GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you
Put your whole query as a sub query then get the sum of TotalVisits.(4+6+12) as it is in your example
Select SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP
) x
|||hi.
thanks for code, but if i write this i'll lost the other columns "TotalVisit" and "Visitor" :(
|||Try this:
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)
GROUPBY UserName
UNION ALL
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE(ProductID= @.ProductID)AND(UserNameISNULL)
GROUPBY AnonimIP
SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_2
WHERE(ProductID= @.ProductID)
|||Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP
) x
Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.
You might want to try this instead:
Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP
) x
|||
hi
david wendelken:
Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP) x
i get this error:Column 'x.Visitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP) x
i get this error:Each GROUP BY expression must contain at least one column that is not outer reference
i am sorry i don't know sql at all:(
|||hi
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE(ProductID= @.ProductID)AND(AnonimIPISNULL)
GROUPBY UserName
UNION ALL
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE(ProductID= @.ProductID)AND(UserNameISNULL)
GROUPBY AnonimIP
SELECT'Total'AS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_2
WHERE(ProductID= @.ProductID)
i get this warrning:Error in GROUP BY clause.
Error in list of function arguments: 'AS' not recognized.
Unable to parse query text.
Select Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY UserName
UNION
SELECT AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY AnonimIP
) x
GROUP BY Visitor
Of course, if I decided to give you a username of 101.123.5.15, you would think I was an IP address.
You might want to try this instead:
Select VisitorType, Visitor, SUM(x.TotalVisit) [TotalVisits]FROM
(
SELECT 'UserName' as VisitorType, UserNameAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisits
WHERE (ProductID = @.ProductID)AND (AnonimIPISNULL)
GROUP BY 'UserName', UserName
UNION
SELECT 'AnonimIP' as VisitorType, AnonimIPAS Visitor,COUNT(VisitID)AS TotalVisit
FROM UserVisitsAS UserVisits_1
WHERE (ProductID = @.ProductID)AND (UserNameISNULL)
GROUP BY 'AnonimIP', AnonimIP
) x
Group by Visitor
hi, thanks for code
that stored procedure returns exacly what is in the SELECT clause: :(
to simplify the problem, i will make a separate stored procedure for storing the total comments...i don't thnik is big deal another comand to database
thanks
|||Glad that worked for you! Be sure to mark which answer(s) worked, so others will know too!
There is a general lesson to learn here about sql: think in sets of data, not rows of data.
By putting parentheses around our select statement, we created a set of data.
Then we queried from that set.
If you can properly define the sets of data that you need, the sql often becomes extremely simple.
Counting total number of queries executed within the page
Hi everyone,
Does exist an easy way to count the actually number of queries executed within a page?
I've searched here and in google but found anything...
Thanks in advance!
If SQL Server is your backend database, you can use SQL Profiler to run a trace on the statements executed.
The db is sql server 2000, but it's on a shared server.. can I use the profiler in a shared environment too?
In case I can use it, does it aggregate someqhat the queries per ASP.NET page executed?
Thanks!
|||>can I use the profiler in a shared environment too?
You can limit it by database.
>In case I can use it, does it aggregate someqhat the queries per ASP.NET page executed?
Given the right option it will report each query run.
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 the inserts and updates on a table in a sql server database
Hello,
Can someone point me to getting the total number of inserts and updates on a table
over a period of time?
I just want to measure the insert and update activity on the tables.
Thanks.
- VishOn a single statement you can capture the @.@.rowcount into a variable and
write it to a log table. But if I'm reading this correctly, you don't want
to do this through the existing code base. Inserts are usually easy if
there is a primary or unique key. Assuming no deletes, simply how many new
keys are there since the last count. Or if the key is incrementing by one
what's the max value - the previous max value. Updates are more vague. How
many rows were updated or how many updates occurred These activities are
usually accommodated for in the initial table design with flag and
last_mod_date columns. Without auditing written into every piece of code or
proper schema design it's an ugly intensive task to rub to data sets
together (using checksums or straight comparisons) to find differences.
Danny
"Viswanatha Thalakola" <vthalakola@.yahoo.com> wrote in message
news:d762e418.0411301845.7504b0b4@.posting.google.c om...
> Hello,
> Can someone point me to getting the total number of inserts and updates on
> a table
> over a period of time?
> I just want to measure the insert and update activity on the tables.
> Thanks.
> - Vish|||On 30 Nov 2004 18:45:55 -0800, Viswanatha Thalakola wrote:
>Hello,
>Can someone point me to getting the total number of inserts and updates on a table
>over a period of time?
>I just want to measure the insert and update activity on the tables.
>Thanks.
>- Vish
Hi Vish,
The easiest way to do this is to add some counting logic to the stored
procedures that do the inserting, updating and deleting. But if you can't
or won't change those tables (or if you allow direct data modifications,
without using stored procedures), you have two other options:
1. Set up a profiler trace. Catch the trace results in a table or in a
file, then use either SQL queries (if in a table) or text manipulation
tools (if in a file) to count the number of inserts, updates, etc. I must
add that I don't know the exact format and I'm not sure either if the
number of rows affected is included in the trace data (it it isn't, you
can't use this approach).
2. Create triggers for each table you need to monitor. Have these triggers
copy @.@.rowcount in a local variable as the first statement (that yields
the number of rows affected by the statement that fired the trigger) and
save that value to a table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> 1. Set up a profiler trace. Catch the trace results in a table or in a
> file, then use either SQL queries (if in a table) or text manipulation
> tools (if in a file) to count the number of inserts, updates, etc. I must
> add that I don't know the exact format and I'm not sure either if the
> number of rows affected is included in the trace data (it it isn't, you
> can't use this approach).
It isn't, but you can catch number of page writes. Still, though, not a
wholly reliable number.
Then again, I assume that the aim is not to save exact numbers, but get
some approxamite statistics, so some Profiler method is proably better
than adding triggers to the system.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, February 17, 2012
Counting Rows
Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?A procedure? Number of rows returned? From where/what?
@.@.rowcount - Returns the number of rows affected by the last statement.
@.@.rowcount - Returns the number of rows affected by the last statement.
Counting Results in a field in SQL 2000
I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?
Thanks
Quote:
Originally Posted by Alpenk
I've got a varchar field and I'm trying to count the whole field and obtain a total count for the number of entries in a particular field. Can someone please provide the syntax on how to display a count of all the results in a field?
Thanks
Please send an example of your table or tables
|||select count(column_name) from table_nameIn this case you count all not null values in this column.
If you want to count distinct values in this colun do following:
Select count(distinct column_name) from table_name
Good Luck.
Counting problem
Hi everyone,
another problem:
I'm trying to count the number of rows but it's not working. Here's my code:
SELECT 'TOTAL number of rows', count(*) --This counts 4! The total number of rows in [Activites]
FROM [Activities]
WHERE [Person ID] IN
(
SELECT DISTINCT [Person ID] --This brings back 2 rows (two specific people)
FROM [Activites]
)
As my comments say, I'm wanting to count the two rows but it's counting every row. Obviously I'm doing something wrong but I can't work it out.
Any help?
AndrewHi,
i hope the following query will solve your issue.
select count(*) from activities group by personid
Regards
Ravi|||It's actually doing exactly what you are asking it to do: count the total number of rows in activities where personid exists inthe activities table.
What you REALLY want it to do is to count the DISTINCT people in that table.
Try:
SELECT 'TOTAL number of rows', count(DISTINCT PersonID)
FROM [Activities]
The GROUP BY solution works great, too...|||Thanks heaps for the feedback!!
Cheers
Andrew
Counting parent records and displaying Total?
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls
---- ---
desktop\pp 5
desktop\qq 6 {This is what I am getting at this stage}
desktop\tt 4
network\rr 9
network\gg 10
software\vv 3
This is what I would like to have:
parameter # Calls
---- ---
desktop 15
network 19
software 3
Please Help!select p.*, c.*, (p.amt+c.amt) as "Total Amt"
from parent p
INNER JOIN
(select id, sum(amt) as amt
from child c
group by id) c ON
p.id = c.id;|||Sorry, but I Forgot to mention that the second column is a count of the occurances in the 1st column. The query has to be run on only one field.
I am attaching my query:
SELECT workitem_category_tree_value,
count(*) as '# Work Items'
FROM dbo.workitem_detail_view hd1
WHERE hd1.[workitem_is_last] = 1
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%')
GROUP BY hd1.[workitem_category_tree_value]
Then this result is diplayed:
Desktop 8
Desktop\Administration 12
Desktop\Administration\Reset Password 42
Desktop\Administration\Unlock Account 30
Desktop\Notebook\LAN 1
Desktop\Notebook\LAN\Join to Domain 2
Desktop\Notebook\LAN\Network Connection 1
Desktop\Notebook\RAS 1
Desktop\Notebook\RAS\Configure 5
parameter # Calls
---- ---
desktop\pp 5
desktop\qq 6 {This is what I am getting at this stage}
desktop\tt 4
network\rr 9
network\gg 10
software\vv 3
This is what I would like to have:
parameter # Calls
---- ---
desktop 15
network 19
software 3
Please Help!select p.*, c.*, (p.amt+c.amt) as "Total Amt"
from parent p
INNER JOIN
(select id, sum(amt) as amt
from child c
group by id) c ON
p.id = c.id;|||Sorry, but I Forgot to mention that the second column is a count of the occurances in the 1st column. The query has to be run on only one field.
I am attaching my query:
SELECT workitem_category_tree_value,
count(*) as '# Work Items'
FROM dbo.workitem_detail_view hd1
WHERE hd1.[workitem_is_last] = 1
AND UPPER(hd1.[workitem_category_tree_value]) LIKE UPPER('%')
GROUP BY hd1.[workitem_category_tree_value]
Then this result is diplayed:
Desktop 8
Desktop\Administration 12
Desktop\Administration\Reset Password 42
Desktop\Administration\Unlock Account 30
Desktop\Notebook\LAN 1
Desktop\Notebook\LAN\Join to Domain 2
Desktop\Notebook\LAN\Network Connection 1
Desktop\Notebook\RAS 1
Desktop\Notebook\RAS\Configure 5
Tuesday, February 14, 2012
Counting all the characters in a column
I'm a SQL newbie...'nuf said.
How do I use a SELECT statement to count all the characters in a given column? I would like to calcuate the total number of characters, the average length, etc.
I can use the LEN function to get a single row like this:
SELECTLEN(NAME1)
FROM INDEXINFO
WHERE NDX = 101
I played around with the COUNT function but that seems to be for counting rows. In VB I would use a FOR EACH clause and keep a running total for the result of the LEN function. Is this a problem better handled using a T-SQL script or can it be done with SELECT statement?
Thanks!
DeBug
Umm...I changed my key words in my Google search and figured out the following:
SELECTSUM(LEN(NAME1))FROM INDEXINFO
WHERE NDX < 100
Ok, this got me the total and I can of course change the WHERE clause but can I also get the AVE at the same time?
|||SELECT SUM(...), AVG(...) FROM INDEXINFOWHERE NDX < 100|||
There are problems with using LEN(). See this thread for a recent discussion.
You could do the following:
Code Snippet
SELECT
ColumnLength = max( datalength( Name1 )),
ColumnAvg = avg( datalength( Name1 ))
FROM IndexInfo
WHERE NDX = 101|||Thank you for your reply!|||
Hi Arnie,
You have to take in mind if the column is a unicode one, because for those, every character is 2 bytes.
AMB
|||Arnie,
Thank you and Phil for helping me with this. Here is the final version:
SELECT
Name1 =sum(datalength( Name1 )),
Name2 =sum(datalength( name2 )),
Name3 =sum(datalength( name3 )),
Name4 =sum(datalength( name4 )),
Date =sum(datalength( date ))
FROM IndexInfo
SELECT
Name1 =avg(datalength( Name1 )),
Name2 =avg(datalength( name2 )),
Name3 =avg(datalength( name3 )),
Name4 =avg(datalength( name4 )),
Date =avg(datalength( date ))
FROM IndexInfo
btw, how do you do that code snippet?|||
I must have trailing spaces somewhere....I used both the len and datalength for the results to text:
Name1 Name2 Name3 Name4 Date
-- -- -- -- --
644653 1047334 59933 69820 888709
(1 row(s) affected)
Name1 Name2 Name3 Name4 Date
-- -- -- -- --
644597 1047332 59933 69820 888709
|||Look for the rows where the LEN() and DATALENGTH() don't match.|||Great Reminder Alejandro,
DATALENGTH() reports the number of bytes used to store the field value, so when using DATALENGTH() with unicode (nchar(), nvarchar()) datatypes, it is necessary to divide by two if you are handling single byte characters.
|||I wrapped the blanks in a delimiter to make it show a little better. I now need to review the data entry code to see why it let someone key all those blank spaces 
Thanks again to all that helped!
DeBug
SELECT NDX, MY_BLANKS = ('-->' + NAME1 + '<--') FROM INDEXINFO
WHERE DATALENGTH(NAME1) > LEN(NAME1);
NDX MY_BLANKS
-- --
263 --> <--
427 --> <--
(2 row(s) affected)
Subscribe to:
Posts (Atom)