Query by year group by total students sort by total for each county

[Rob]

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

Rob

[David Portas]

Something like this, maybe:

SELECT county, YEAR(registration_date), SUM(num_students) AS tot_students
FROM Students
GROUP BY county, YEAR(registration_date)
ORDER BY county, tot_students

If you need more help please post DDL (CREATE TABLE statement) for the
table, including keys and constraints.

--
David Portas
------------
Please reply only to the newsgroup
--

[Rob]

th************@yahoo.com (Rob) wrote in message news:<14*************************@posting.google.c om>...

I haven't a clue how to accomplish this.
All the data is in one table. The data is stored by registration date
and includes county and number of students brokne out by grade.

Any help appreciated!

Rob

Here's the CREATE TABLE
CREATE TABLE [dbo].[EdSales] (
[FName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OrgName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[extension] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [nvarchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOV] [datetime] NULL ,
[grade1] [numeric](10, 0) NULL ,
[grade2] [numeric](10, 0) NULL ,
[grade3] [numeric](10, 0) NULL ,
[grade4] [numeric](10, 0) NULL ,
[grade5] [numeric](10, 0) NULL ,
[grade6] [numeric](10, 0) NULL ,
[grade7] [numeric](10, 0) NULL ,
[grade8] [numeric](10, 0) NULL ,
[grade9] [numeric](10, 0) NULL ,
[grade10] [numeric](10, 0) NULL ,
[grade11] [numeric](10, 0) NULL ,
[grade12] [numeric](10, 0) NULL ,
[grade13] [numeric](10, 0) NULL ,
[grade14] [numeric](10, 0) NULL ,
[grade15] [numeric](10, 0) NULL ,
[grade16] [numeric](10, 0) NULL ,
[grade17] [numeric](10, 0) NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateEntered] [datetime] NULL ,
[result] [numeric](18, 0) NULL ,
[ConfirmNum] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[visible] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [PK_EdSales_1] PRIMARY KEY CLUSTERED
(
[ConfirmNum]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[EdSales] WITH NOCHECK ADD
CONSTRAINT [DF_EdSales_visible] DEFAULT ('y') FOR [visible]
GO

[David Portas]

Your table design has some problems: Non-normalised repeating group of
Grades; No natural primary key; Too many NULLable columns (if every column
can be NULL then why would you have a row in the table anyway!). This query
would be a lot simpler if you fixed these things.

SELECT county, YEAR(dateentered) AS year_entered,
SUM(
COALESCE(grade1,0)+
COALESCE(grade2,0)+
COALESCE(grade3,0)+
COALESCE(grade4,0)+
COALESCE(grade5,0)+
COALESCE(grade6,0)+
COALESCE(grade7,0)+
COALESCE(grade8,0)+
COALESCE(grade9,0)+
COALESCE(grade10,0)+
COALESCE(grade11,0)+
COALESCE(grade12,0)+
COALESCE(grade13,0)+
COALESCE(grade14,0)+
COALESCE(grade15,0)+
COALESCE(grade16,0)+
COALESCE(grade17,0))
AS tot_students
FROM EdSales
GROUP BY county, YEAR(dateentered)

--
David Portas
------------
Please reply only to the newsgroup
--

[Rob]

"David Portas" <RE****************************@acm.org> wrote in message news:<_d********************@giganews.com>...

Your table design has some problems: Non-normalised repeating group of
Grades; No natural primary key; Too many NULLable columns (if every column
can be NULL then why would you have a row in the table anyway!). This query
would be a lot simpler if you fixed these things.

SELECT county, YEAR(dateentered) AS year_entered,
SUM(
COALESCE(grade1,0)+
COALESCE(grade2,0)+
COALESCE(grade3,0)+
COALESCE(grade4,0)+
COALESCE(grade5,0)+
COALESCE(grade6,0)+
COALESCE(grade7,0)+
COALESCE(grade8,0)+
COALESCE(grade9,0)+
COALESCE(grade10,0)+
COALESCE(grade11,0)+
COALESCE(grade12,0)+
COALESCE(grade13,0)+
COALESCE(grade14,0)+
COALESCE(grade15,0)+
COALESCE(grade16,0)+
COALESCE(grade17,0))
AS tot_students
FROM EdSales
GROUP BY county, YEAR(dateentered)

Well, I am a newbie at this sort of a thing. So, any pointers to
useful info on the diffiencies you mention are appreciated.