467,858 Members | 1,704 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,858 developers. It's quick & easy.

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
Jul 20 '05 #1
  • viewed: 3587
Share:
4 Replies
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
--
Jul 20 '05 #2
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
Jul 20 '05 #3
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
--
Jul 20 '05 #4
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.
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by hrishy | last post: by
8 posts views Thread by Tony Williams | last post: by
1 post views Thread by Robert | last post: by
3 posts views Thread by Dave | last post: by
7 posts views Thread by Dan | last post: by
3 posts views Thread by Richard Hollenbeck | last post: by
9 posts views Thread by JJM0926 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.