sql query help - count and group by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query help - count and group by

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-19 : 13:48:10

i have a query

select count(id) from anp where ((status!='100% Permit' and status!='repeat') or status is null) and ((stage=1 and possibleviolator=1) or stage=0)

now instead of the count of total records I want to know how many days and sites there are.
so basically a count of the number of records that would show if I grouped by mydate and site.

Can someone help me with this please??

table is

CREATE TABLE [dbo].[anp](
[Plate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[D] [datetime] NULL,
[Site] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Code] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Direction] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Status] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[stage] [int] NULL CONSTRAINT [DF_anPossibleViolators_stage] DEFAULT ((0)),
[possibleviolator] [bit] NULL CONSTRAINT [DF_anPossibleViolators_possibleviolator] DEFAULT ((0)),
[confirmedviolator] [bit] NULL CONSTRAINT [DF_anPossibleViolators_confirmedviolator] DEFAULT ((0)),
[id] [int] IDENTITY(1,1) NOT NULL,
[mydate] [datetime] NULL CONSTRAINT [DF_anPossibleViolators_mydate] DEFAULT (getdate()),
[mydatestage2] [datetime] NULL,
[mydatestage1] [datetime] NULL,
[inserted] [bit] NULL CONSTRAINT [DF_anPossibleViolators_inserted] DEFAULT ((0))
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-19 : 15:25:48

SELECT COUNT(DISTINCT Site), COUNT(DISTINCT MyDate)
FROM dbo.ANP

maybe?

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-20 : 01:16:24

I don't want 2 numbers - i want to count by distinct date and site

such as count(distinct site,mydate)

but that's not valid

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 01:53:38

Try

Select site,mydate,count(*) from table group by site,mydate

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-20 : 02:00:51

much closer

2 issues

1. mydate is a date time field -- i want it only grouped by date and not time (so each day is counted dif but not each minute

2. i want the number or records returned in this query but not the count

thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 02:32:47

1
Select site,dateadd(day,datediff(day,0,mydate),0) as mydate ,count(*) from table group by site,dateadd(day,datediff(day,0,mydate),0)

2 Is there a difference?

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-20 : 02:44:01

thanks --well how can i get the count of the rows returned vs count?

if it helps i'm pulling it from a stored procedure -- can i get the recordcount from the query returned to a variable in stored procedure

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-08-20 : 03:05:07

thanks for your help - i figured it out.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-20 : 03:57:33