count records in a top 10 query

Hi

Im trying to make a top 10 list of col1 and and at the 11:th place it should show a number of record that dosent make it to the top 10 list...

i have this so far, and it dosent give me anything...

col1 is varchar 254

SELECT COL1, COUNT(*) AS number
FROM MYTABLE
WHERE (NOT EXISTS
(SELECT TOP 10 COL1
FROM MYTABLE))
GROUP BY COL1
ORDER BY COUNT(*) DESC)

ex of output

place1 100
place2 50
place3 25
...
place11 500

a query that only gives me the place11 number is enough

thx in advance //MrHere is the number of records that are not in the top 10 list

select count(*) number
from myTable
where col1 not in
(select top 10 col1 from myTable)
group by col1
order by count(*) desc
)

Count Records Help

Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));Although I dont have the exact answer are you trying to do the following

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 2
B 12000 9 6
B 8000 2 2
B 16000 3 7

select conf, count(*) / (select count(*) from table)
from table
where self_score > opp_score
group by conf;

This query doesn't work because you need to group the divided query into the same group as the outer query. I would also like to know how this is done.|||Greetings,

I now have the answer :),

conf = conference

select s1.conf, avg_att As "Avg. Att", (wins / total) * 100 As "% Won"
from
(select conf, count(*) As wins
from help
where self_score > opp_score
group by conf) s1,
(select conf, avg(att) As avg_att, count(*) As total
from help
group by conf) s2
where s1.conf = s2.conf;

The average attendance is for the entire conference group regardless of whether or not they won. If you only want average attendance for the games they played then select the avg(att) in the top from-query as opposed to the second.

The sample table I used for this is shown below,

SQL> select * from help;

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 0
B 12000 4 6
B 8000 2 0
B 16000 3 7

And the results generated from the answer query

CONF Avg. Att % Won
---- ---- ----
A 7066.66667 66.6666667
B 12000 33.3333333

Cheers.|||- Thanks for your reply, It appears I left some info out, Ive been trying to adapt what you replied with but still no dice.

I am including the tables and data below. There are some null values as I am only inputing sample data that is needed in the reports.

CREATE TABLE SCHOOL
(school VARCHAR2(30),
conference VARCHAR2(25),
stadium_size NUMBER(6),
ticket_price NUMBER(4,2),
in_state_players NUMBER(2),
outstate_players NUMBER(2),
scholarships NUMBER(2),
graduate NUMBER(20));

INSERT INTO school VALUES
('Indiana Univ.', 'Big Ten', 53000, null, null, null, null, null);
INSERT INTO school VALUES
('Ohio State Univ.', 'Big Ten', 104000, null, null, null, null, null);
INSERT INTO school VALUES
('Penn State Univ.', 'Independent', 80000, null, null, null, null, null);
INSERT INTO school VALUES
('Univ. of Pittsburgh', 'Independent', 51000, null, null, null, null, null);
INSERT INTO school VALUES
('Pondunk Univ.', 'Independent', 44000, null, null, null, null, 35);
INSERT INTO school VALUES
('Violator Univ.', 'Independent', 39000, null, null, null, null, 47);

CREATE TABLE SCHOOL_INCIDENTS
(school VARCHAR2(30),
idate DATE,
incident_code NUMBER(5));

INSERT INTO school_incidents VALUES
('Indiana Univ.',null, 17983);
INSERT INTO school_incidents VALUES
('Ohio State Univ',null, 12891);
INSERT INTO school_incidents VALUES
('Penn State Univ.',null, 17250);

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 41000, 0, 7, null, null);

Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Big Ten 46000 17983
Big Ten 45000 17983
Big Ten 44000 17983
Big Ten 43000 17983
Big Ten 42000 17983
Big Ten 41000 17983
Big Ten 40000 17983
Big Ten 39000 17983
Big Ten 38000 17983
Big Ten 37000 17983
Big Ten 36000 17983

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Independent 51000 17250
Independent 50000 17250
Independent 49000 17250
Independent 48000 17250
Independent 47000 17250
Independent 46000 17250
Independent 45000 17250
Independent 44000 17250
Independent 43000 17250
Independent 42000 17250
Independent 41000 17250

Count Query Question

I have a table that I am trying to do a query on.

Table is named GPFCount2.

CREATE TABLE [GPFCount2] (
[WeekID] [int] NULL ,
[BeginDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Region] [int] NULL ,
[Unit] [int] NULL ,
[GPFCount] [int] NULL
) ON [PRIMARY]

For example:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14

The above says that unit 92 had 14 GPFs during the week of 11/11/2006
3PM to 11/18/2006 2:59:59 PM. Unit 192 is part of region 8000. The
time period covered was week 30.

What I want to see is the number of times the unit has been in the top
25 list over the last 5 weeks. Unit 192 is in the top 25 list for
Weeks, 30, 29, 28, and 26.

So my result set for this unit should be:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14, 4

The 4 being the number of times in the last 5 weeks that unit 192 was
in the top 25.

And then for Week 29, assuming unit 192 is in the top 25 for weeks
29,28 and 26 (and not 27 or 25), then it would be 3. And the results
from the query would be:
29 ,'11/04/2006 15:00:00','11/11/2006 14:59:59', 8000 , 192 , 14, 3

This is the query I was working with, but it's not working. I'm not
too sure how to make this work.

SelectA.weekid,
A.begindate,
A.EndDate,
A.region,
A.unit,
A.gpfcount,
B.UnitCount

Quote:

Originally Posted by

>From gpfcount2 A

Join
(SelectWeekID,
Unit,
Count(Unit) UnitCount
From gpfcount2
Where WeekID Between WeekID - 4 and WeekID
Group By Unit,WeekID
) B
On A.Unit = B.Unit

Thanks,
Jennifer

INSERTS FOR TABLE (There are inserts only for weeks 30 through 20 for
brevity's sake):

insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 898 , 22
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 777 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 846 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 907 , 20
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 608 , 18
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 40 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 107 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 723 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 60 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 78 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 300 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 317 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 658 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 719 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 782 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 2 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 192 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 362 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 456 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 609 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 182 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 712 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 588 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 191 , 19
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 498 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 192 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 445 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 742 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 532 , 15
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 540 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 184 , 13
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 288 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 313 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 78 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 598 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 610 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 840 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 918 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 221 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 452 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 594 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 608 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 706 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 35 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 112 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 218 , 8
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 542 , 30
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 35 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 695 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 924 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 533 , 25
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 878 , 18
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 12 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 139 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 698 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 458 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 528 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 740 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 911 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 778 , 14
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 192 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 738 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 2 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 176 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 450 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 571 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 7000 , 715 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 925 , 12
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 123 , 34
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 192 , 32
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 264 , 19
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 601 , 18
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 875 , 17
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 550 , 16
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 7000 , 761 , 15
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 141 , 14
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 3 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 745 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 750 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 190 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 506 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 533 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 899 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 903 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 175 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 300 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 311 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 397 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 450 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 597 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 743 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 878 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 782 , 20
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 192 , 19
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 317 , 18
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 695 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 85 , 15
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 190 , 14
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 592 , 13
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 439 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 576 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 509 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 563 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 280 , 10
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 123 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 337 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 388 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 601 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 698 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 7000 , 715 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 812 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 832 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 368 , 8
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 490 , 8
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 777 , 26
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 907 , 22
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 597 , 18
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 285 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 439 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 781 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 906 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 12 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 745 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 748 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 889 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 192 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 749 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 755 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 107 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 443 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 540 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 595 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 839 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 907 , 29
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 12 , 25
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 695 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 777 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 778 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 788 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 439 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 566 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 723 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 7000 , 774 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 40 , 15
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 396 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 175 , 13
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 336 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 498 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 781 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 829 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 140 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 311 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 448 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 514 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 791 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 139 , 10
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 551 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 788 , 33
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 723 , 24
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 192 , 18
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 397 , 15
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 166 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 498 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 695 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 694 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 396 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 708 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 733 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 439 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 527 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 550 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 7000 , 217 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 399 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 425 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 609 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 728 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 787 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 131 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 604 , 28
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 223 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 723 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 724 , 17
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 598 , 15
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 3 , 14
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 619 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 397 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 540 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 490 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 498 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 658 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 782 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 823 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 334 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 774 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 870 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 43 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 549 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 192 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 443 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 527 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 566 , 8
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 407 , 21
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 451 , 20
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 723 , 19
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 755 , 17
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 286 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 336 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 778 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 89 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 445 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 176 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 292 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 324 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 480 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 715 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 201 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 396 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 469 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 578 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 724 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 132 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 262 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 288 , 9
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 723 , 33
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 550 , 27
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 2 , 25
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 349 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 911 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 829 , 18
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 782 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 320 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 7000 , 587 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 788 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 796 , 14
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 81 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 501 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 292 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 799 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 430 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 450 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 790 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 898 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 399 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 745 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 750 , 10Count Query Question|||Count Query Question|||Roy Harvey wrote:

Quote:

Originally Posted by

AND B.WeekID BETWEEN A.WeekID - 4 and B.WeekID

Did you mean A.WeekID there at the end?

Count query problem :(

Hi all,

Hope someone can help me with this ...

This is my query. The problem with it is that it only returns Areas with >0
Topics. So if a new Area is created, it hasn't a topic until one is
created, and it doesn't list!

Is it possible to return even those rows where the count is 0, or what is
another way around this?

Thanks in advance, Jen

SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated,
COUNT(dbo.chat_topic.topicID) AS counttopics
FROM dbo.chat_board INNER JOIN
dbo.chat_area ON
dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
dbo.chat_topic ON
dbo.chat_area.areaID = dbo.chat_topic.areaID
GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
dbo.chat_area.name, dbo.chat_area.datecreated

--
Fast Track On Line -Web Design and Development
Portfolio http://www.fasttrackonline.co.uk

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004On Wed, 2 Jun 2004 11:55:27 +0100, Jenny wrote:

>Hi all,
>Hope someone can help me with this ...
>This is my query. The problem with it is that it only returns Areas with >0
>Topics. So if a new Area is created, it hasn't a topic until one is
>created, and it doesn't list!
>Is it possible to return even those rows where the count is 0, or what is
>another way around this?
>Thanks in advance, Jen
>SELECT dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated,
> COUNT(dbo.chat_topic.topicID) AS counttopics
>FROM dbo.chat_board INNER JOIN
> dbo.chat_area ON
> dbo.chat_board.boardID = dbo.chat_area.boardID INNER JOIN
> dbo.chat_topic ON
> dbo.chat_area.areaID = dbo.chat_topic.areaID
>GROUP BY dbo.chat_board.boardID, dbo.chat_area.areaID,
> dbo.chat_area.name, dbo.chat_area.datecreated

Hi Jenny,

Try replacing the second "INNER JOIN" with "LEFT OUTER JOIN". Check Books
Online for the details.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

count query problem

I have a table with a list of clients, some tasks associated with each and the status of each task (open, closed).

I want to return a SINGLE row for each client, along with a COUNT of the number of "open" and "closed" tasks.

The typical count statement I am using is this:

select SiteName, Count(IssueStatus) as cnt, IssueStatus,
from IssueMaster
group by SiteName, IssueStatus

It works fine, however, it returns a row for each combination of client and status. I would like it to return "Client, Count of Open, Count of Closed".

Any ideas?It depends on which SQL dialect you are using, but I'd use Sum() combined with CASE instead of Count().

-PatP|||I'm using MSSQL, but can I SUM a non-numeric column? And do you have an example I could use as a model?

Thanks!|||Like this:

SUM (CASE WHEN status='OPEN' THEN 1 ELSE 0 END) AS open_count|||Or still using COUNT:

COUNT (CASE WHEN status='OPEN' THEN 1 END) AS open_count

Count Query Performance

I have a query that seems simple but apparently is not.Any thoughts on performance optimization would be helpful.The following query works, but it takes 20 minutes to run.

WITH

MEMBER [Measures].[90-100] AS

'COUNT( FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.90)

)'

MEMBER [Measures].[80-90] AS

'COUNT(FILTER( EXISTING [Student].[Student].[Student].Members, [Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)'

SELECT

{[Measures].[90-100], [Measures].[80-90]} ON COLUMNS,

NON EMPTY EXISTING [School].[By District Area].[School].MEMBERS ON ROWS

FROM [Student Engagement]

There has got to be a better way. My guess is that somehow I need to limit the resulting set of schools * students that get counted to only the nonempties, but I’m not sure how to formulate the MDX.

Any ideas?

Chris

Instead of EXISTING in the calculated measures, which isn't needed unless other attributes of the [Student] dimension are placed on query axes, you could try NonEmpty(), like:

WITH

MEMBER [Measures].[90-100] AS

COUNT( FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.90)

)

MEMBER [Measures].[80-90] AS

COUNT(FILTER( NonEmpty([Student].[Student].[Student].Members,

{[Measures].[AttendFactCount]}),

[Measures].[Avg Attendance] >.80

AND [Measures].[Avg Attendance] <.90)

)

where [AttendFactCount] is some cube measure from the relevant measure group (assuming that [Avg Attendance] is a calculated measure like: [PresentSum] / [AttendFactCount]).|||

Deepak-

Thank you! Inserting the “NonEmpty” worked. The query returned in 5 seconds.

The question is why.Given, that I had “Non Empty” in the select statement and the NonEmptyBehavior(NEB) set to [Measures].[Absence] for the calculated member [Measures].[Avg Attendance] (Avg Attendance = Absence/ Enrollment) in the initial query,I would have assumed that the query engine would suppress the non empties automatically based on the NEB.Can you help me understand?

Chris

|||

Chris,

The “Non Empty” in the select statement wouldn't help because, if the 2 calculated measures are simply defined as counts, they could be 0, but never empty. Regarding the NEB, I'm not sure; but it could be that the query optimizer doesn't infer from the condition: [Avg Attendance] > 0.9 that [Avg Attendance] isn't empty. You could check whether changing the condition to: Not IsEmpty([Avg Attendance]) And [Avg Attendance] > 0.9 speeds up the query.

Count Query Help

I am not too good at programming but I need to get the
names and the count of the selected tables (Tables that
starts with aa). I should have the following fromat.
Tablename Count
table1 256
table2 347
..... ...
I wrote a while statement but my select at the end don't
work.
Can anyone show me how my query should look like '
Thanks for any help..There isn't any queries that I know of that will return the table names.
This would have to be done in program code there is probable an API for it,
however the query for the count is simple if you can get the table name (in
code) beginning with aa:
SELECT count(*) FROM TableName
Hope it helps
Gav
"George" <anonymous@.discussions.microsoft.com> wrote in message
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the
> names and the count of the selected tables (Tables that
> starts with aa). I should have the following fromat.
> Tablename Count
> table1 256
> table2 347
> ..... ...
> I wrote a while statement but my select at the end don't
> work.
> Can anyone show me how my query should look like '
> Thanks for any help..|||Here is a basic rowcount routine:
use pubs
go
SELECT A.name, B.rows=20
FROM sysobjects A
JOIN sysindexes B ON A.ID =3D B.ID
WHERE A.type =3D 'U'
AND B.INDID < 2=20
ORDER BY A.Name
--=20
Keith
"George" <anonymous@.discussions.microsoft.com> wrote in message =
news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
> I am not too good at programming but I need to get the=20
> names and the count of the selected tables (Tables that=20
> starts with aa). I should have the following fromat.
>=20
> Tablename Count
> table1 256
> table2 347
> ..... ...
>=20
> I wrote a while statement but my select at the end don't=20
> work.
> Can anyone show me how my query should look like '
>=20
> Thanks for any help..|||That did it.
Thanks a lot.

>--Original Message--
>Here is a basic rowcount routine:
>use pubs
>go
>SELECT A.name, B.rows
>FROM sysobjects A
>JOIN sysindexes B ON A.ID = B.ID
>WHERE A.type = 'U'
> AND B.INDID < 2
>ORDER BY A.Name
>--
>Keith
>
>"George" <anonymous@.discussions.microsoft.com> wrote in
message news:8e6801c40841$78eb9ce0$a601280a@.phx.gbl...
don't
>.
>sql

Count query

Hi

I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had

I need to count the no of students for the different Intervention field which are like 14 different types

against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below

A student can be Male and Hispanic Type 1

also

Male Hispanic Type 2

Male Hispanic Type 3

So his count is made in three places

I need to do this

for the whole District level

then for each Center under District level

then for each school under Center level

DISTRICT SUMMARY

Intervention

Total

Male

Female

Asian

Black

Hispanic

Indian

Multiracial

White

TYPE : 1

7

3

4

4

2

0

0

0

1

TYPE :2

6

3

3

1

0

4

0

0

1

TYPE : 3

3

1

2

1

2

0

0

0

0

TYPE : 4

5

2

3

2

3

0

0

0

0

TYPE : 5

1

0

1

1

0

0

0

0

0

TYPE : 7

2

1

1

0

2

0

0

0

0

TYPE : 8

1

1

0

0

0

1

0

0

0

TYPE : 9

2

1

1

0

1

0

0

0

1

TYPE : 14

1

1

0

0

1

0

0

0

0

Please help

Thanks

What does your table(s) structure look like?

|||

Hi

I am joining these TWO tables based on student ID

The fields Ethnicity and field Gender both come from one table Table A

The field Intervention Types comes from Table B

Table B is

a group of three tables because the table that has intervention types does not have student_id

so I need to link it via a common field with another table

This below is how i am working on the code

select

SUBQUERY.student_id,
SUBQUERY.Intervention_ID,
STDM.student_ethnicity,
STDM.student_gender,

count( case when STDM.STUDENT_ETHNICITY in('Asian') then STDM.student_id else NULL end) as [Asian],

count( case when STDM.STUDENT_ETHNICITY in ('Black') then STDM.student_id else NULL end) as [Black],

count( case when STDM.STUDENT_ETHNICITY in('Hispanic') then STDM.student_id else NULL end) as [Hispanic],

count( case when STDM.STUDENT_ETHNICITY in('American Indian') then STDM.student_id else NULL end) as [American Indian],

count( case when STDM.STUDENT_ETHNICITY in('Multiracial') then STDM.student_id else NULL end) as [Multiracial],

count( case when STDM.STUDENT_ETHNICITY in('White') then STDM.student_id else NULL end) as [White],

count( case when STDM.STUDENT_GENDER in('M') then STDM.student_id else NULL end) as [Male],

count( case when STDM.STUDENT_GENDER in('F') then STDM.student_id else NULL end) as [Female]

from DW_student.dbo.Student STDM
right join
(select distinct
student_id,INTV.intervention_id from
WAIN.DBO.Meeting MTNG
inner join WAIN.DBO.Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID
inner join WAIN.DBO.Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID
) SUBQUERY
on SUBQUERY.student_id = STDM.student_id

group by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
SUBQUERY.Intervention_ID,
STDM.STUDENT_ETHNICITY,
SUBQUERY.student_id,
STDM.STUDENT_GENDER
order by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
STDM.STUDENT_ETHNICITY,
SUBQUERY.Intervention_ID asc,
SUBQUERY.student_id,
STDM.STUDENT_GENDER

|||This looks like it would be a lot easier to do in a cube or pivot table. Where type is your dimension on rows and ethnicity is your dimension on columns, and count would be the measure.|||

Can you please explain it relating it to the present example

Thanks

Sowmya

|||

Your logic is motly used when PIVOTing the Columns. I didn't find any table design changes here. Your data is perfect.

You are almost on correct track – Your logic & intention of the expression almost perfect. But you need to change the expression slightly to achive your result,

- Use 0 as ELSE value on the Case When

- Use Sum instead of Count

Here the updated query,

select

subquery.student_id,

subquery.intervention_id,

stdm.student_ethnicity,

stdm.student_gender,

sum(case when stdm.student_ethnicity in ('asian') then 1 else 0 end) as [Asian],

sum(case when stdm.student_ethnicity in ('black') then 1 else 0 end) as [Black],

sum(case when stdm.student_ethnicity in ('hispanic') then 1 else 0 end) as [Hispanic],

sum(case when stdm.student_ethnicity in ('american indian') then 1 else 0 end) as [American indian],

sum(case when stdm.student_ethnicity in ('multiracial') then 1 else 0 end) as [Multiracial],

sum(case when stdm.student_ethnicity in ('white') then 1 else 0 end) as [White],

sum(case when stdm.student_gender in ('m') then 1 else 0 end) as [Male],

sum(case when stdm.student_gender in ('f') then 1 else 0 end) as [Female]

from

dw_student.dbo.student stdm

right join

(

select distinct

student_id,intv.intervention_id

from

wain.dbo.meeting mtng

inner join wain.dbo.meeting_intervention mtgi on mtng.meeting_id = mtgi.meeting_id

inner join wain.dbo.intervention intv on mtgi.intervention_id = intv.intervention_id

) subquery

on subquery.student_id = stdm.student_id

group by

stdm.school_region,

stdm.school_number,

stdm.school_name,

subquery.intervention_id,

stdm.student_ethnicity,

subquery.student_id,

stdm.student_gender

order by

stdm.school_region,

stdm.school_number,

stdm.school_name,

stdm.student_ethnicity,

subquery.intervention_id asc,

subquery.student_id,

stdm.student_gender

Count query

Hi

I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had

I need to count the no of students for the different Intervention field which are like 14 different types

against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below

A student can be Male and Hispanic Type 1

also

Male Hispanic Type 2

Male Hispanic Type 3

So his count is made in three places

I need to do this

for the whole District level

then for each Center under District level

then for each school under Center level

DISTRICT SUMMARY

Intervention

Total

Male

Female

Asian

Black

Hispanic

Indian

Multiracial

White

TYPE : 1

7

3

4

4

2

0

0

0

1

TYPE :2

6

3

3

1

0

4

0

0

1

TYPE : 3

3

1

2

1

2

0

0

0

0

TYPE : 4

5

2

3

2

3

0

0

0

0

TYPE : 5

1

0

1

1

0

0

0

0

0

TYPE : 7

2

1

1

0

2

0

0

0

0

TYPE : 8

1

1

0

0

0

1

0

0

0

TYPE : 9

2

1

1

0

1

0

0

0

1

TYPE : 14

1

1

0

0

1

0

0

0

0

Please help

Thanks

What does your table(s) structure look like?

|||

Hi

I am joining these TWO tables based on student ID

The fields Ethnicity and field Gender both come from one table Table A

The field Intervention Types comes from Table B

Table B is

a group of three tables because the table that has intervention types does not have student_id

so I need to link it via a common field with another table

This below is how i am working on the code

select

SUBQUERY.student_id,
SUBQUERY.Intervention_ID,
STDM.student_ethnicity,
STDM.student_gender,

count( case when STDM.STUDENT_ETHNICITY in('Asian') then STDM.student_id else NULL end) as [Asian],

count( case when STDM.STUDENT_ETHNICITY in ('Black') then STDM.student_id else NULL end) as [Black],

count( case when STDM.STUDENT_ETHNICITY in('Hispanic') then STDM.student_id else NULL end) as [Hispanic],

count( case when STDM.STUDENT_ETHNICITY in('American Indian') then STDM.student_id else NULL end) as [American Indian],

count( case when STDM.STUDENT_ETHNICITY in('Multiracial') then STDM.student_id else NULL end) as [Multiracial],

count( case when STDM.STUDENT_ETHNICITY in('White') then STDM.student_id else NULL end) as [White],

count( case when STDM.STUDENT_GENDER in('M') then STDM.student_id else NULL end) as [Male],

count( case when STDM.STUDENT_GENDER in('F') then STDM.student_id else NULL end) as [Female]

from DW_student.dbo.Student STDM
right join
(select distinct
student_id,INTV.intervention_id from
WAIN.DBO.Meeting MTNG
inner join WAIN.DBO.Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID
inner join WAIN.DBO.Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID
) SUBQUERY
on SUBQUERY.student_id = STDM.student_id

group by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
SUBQUERY.Intervention_ID,
STDM.STUDENT_ETHNICITY,
SUBQUERY.student_id,
STDM.STUDENT_GENDER
order by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
STDM.STUDENT_ETHNICITY,
SUBQUERY.Intervention_ID asc,
SUBQUERY.student_id,
STDM.STUDENT_GENDER

|||This looks like it would be a lot easier to do in a cube or pivot table. Where type is your dimension on rows and ethnicity is your dimension on columns, and count would be the measure.|||

Can you please explain it relating it to the present example

Thanks

Sowmya

|||

Your logic is motly used when PIVOTing the Columns. I didn't find any table design changes here. Your data is perfect.

You are almost on correct track – Your logic & intention of the expression almost perfect. But you need to change the expression slightly to achive your result,

- Use 0 as ELSE value on the Case When

- Use Sum instead of Count

Here the updated query,

select

subquery.student_id,

subquery.intervention_id,

stdm.student_ethnicity,

stdm.student_gender,

sum(case when stdm.student_ethnicity in ('asian') then 1 else 0 end) as [Asian],

sum(case when stdm.student_ethnicity in ('black') then 1 else 0 end) as [Black],

sum(case when stdm.student_ethnicity in ('hispanic') then 1 else 0 end) as [Hispanic],

sum(case when stdm.student_ethnicity in ('american indian') then 1 else 0 end) as [American indian],

sum(case when stdm.student_ethnicity in ('multiracial') then 1 else 0 end) as [Multiracial],

sum(case when stdm.student_ethnicity in ('white') then 1 else 0 end) as [White],

sum(case when stdm.student_gender in ('m') then 1 else 0 end) as [Male],

sum(case when stdm.student_gender in ('f') then 1 else 0 end) as [Female]

from

dw_student.dbo.student stdm

right join

(

select distinct

student_id,intv.intervention_id

from

wain.dbo.meeting mtng

inner join wain.dbo.meeting_intervention mtgi on mtng.meeting_id = mtgi.meeting_id

inner join wain.dbo.intervention intv on mtgi.intervention_id = intv.intervention_id

) subquery

on subquery.student_id = stdm.student_id

group by

stdm.school_region,

stdm.school_number,

stdm.school_name,

subquery.intervention_id,

stdm.student_ethnicity,

subquery.student_id,

stdm.student_gender

order by

stdm.school_region,

stdm.school_number,

stdm.school_name,

stdm.student_ethnicity,

subquery.intervention_id asc,

subquery.student_id,

stdm.student_gender

Count Query

hi! I am new to this whole SQL language...
had a question about using a count function or if i shuld even be using a count function for this...
Data I have to work with I would like my result set to look like this:
ID ID2 ID ID1Count ID2Count ID3Count ID4Count
A 1 A 2 2 2 3
A 1 B 1 0 3 1
A 2
A 3
A 2
A 4
A 4
A 4
A 3
B 3
B 4
B 1
B 3
B 3
I am stumped.... ANy help would be great... thanks!
nick

select ID,
sum(case when ID2 = 1 then 1 else 0 end) as ID1Count,
sum(case when ID2 = 2 then 1 else 0 end) as ID2Count,
sum(case when ID2 = 3 then 1 else 0 end) as ID3Count,
sum(case when ID2 = 4 then 1 else 0 end) as ID4Count,
from table1
group by ID|||Thanks for the direction! got it to work like a charm!

Count of same column appearing twice in the query

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS MagazineAdCount
FROM orderformlineitems
where product_variant_id = '4010436709979469536'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek MagazineAdCount
04/16/2007 04/23/2007 8
04/23/2007 04/30/2007 15
04/30/2007 05/07/2007 5

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,
CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,
COUNT(*) AS BannerAdCount
FROM orderformlineitems
where product_variant_id = '7453910328410493551'
group by datediff(day,'19000101',d_datecreated)/7
order by StartWeek ASC

Output
StartWeek EndWeek BannerAdCount
04/16/2007 04/23/2007 15
04/23/2007 04/30/2007 21
04/30/2007 05/07/2007 22

I WANT THE BELOW OUTPUT THRU A SINGLE QUERY.....

StartWeek EndWeek MagazineAdCount BannerAdCount

04/16/2007 04/23/2007 8 15
04/23/2007 04/30/2007 15 21
04/30/2007 05/07/2007 5 22

Can anyone help please ?

Thanks

You can join these 2 query as single query..

Select Data1.Startweek,

Data1.EndWeek,

Data1.MagazineAdCount,

Data2.BannerAdCount

From

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS MagazineAdCount

FROM orderformlineitems

where product_variant_id = '4010436709979469536'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data1

Join

(SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

COUNT(*) AS BannerAdCount

FROM orderformlineitems

where product_variant_id = '7453910328410493551'

group by datediff(day,'19000101',d_datecreated)/7 ) as Data2 On Data1.StartWeek = Data2.StartWeek And Data1.EndWeek = Data2.EndWeek

|||Thank you !!|||

Use following query:

Code Snippet

SELECT CONVERT(char(10),min(dateadd(day, datediff(day,'19000101',d_datecreated)/7*7, '19000101')),101) as StartWeek,

CONVERT(char(10),max(dateadd(day, datediff(day,'19000101',dateadd(day,6,d_datecreated))/7*7, '19000101')),101) as EndWeek,

sum ( case product_variant_id when '4010436709979469536' then 1 else 0 end) AS MagazineAdCount,

sum ( case product_variant_id when '7453910328410493551' then 1 else 0 end) AS BannerAdCount

FROM orderformlineitems

where product_variant_id in ( '4010436709979469536', '7453910328410493551')

group by datediff(day,'19000101',d_datecreated)/7

order by StartWeek ASC

|||Thanks !!

Count of related records?

Hi,

I was wondering if it was possible to build a query that will include a
column that will provide a count related records from another table.
Although there is a way to achieve this through programming in the
front end, I would like to know if it possible to achieve the same
thing through a SQL statement alone.

For example, say you have two related tables, Invoices and
InvoiceItems. InvoiceID is the primary key of Invoices.

Invoices table

InvoiceID PO_Num CompanyID
----------
1 37989 3
2 87302 4
3 78942 3

InvoiceItems table

ItemID InvoiceID PartNo Qty
------------
1 1 ABA 3
2 1 ASLKDJ 2
3 1 9LF 8
4 2 IEPOW 18
5 2 EIWPD 3
6 2 DSSIO 1
7 2 EIWP 5
8 2 DC93 4
9 3 85LS0 8

Then a query that has the Invoices table plus a count of InvoiceItems
for each InvoiceID would generate this:

InvoiceID PO_Num CompanyID ItemCount
---------------
1 37989 3 3
2 87302 4 5
3 78942 3 1

Does anyone have any ideas how this would be done?

Thank you.SELECT I.invoiceid, I.po_num, I.companyid,
COALECSE(T.cnt,0) AS itemcount
FROM Invoices AS I
LEFT JOIN
(SELECT invoiceid, COUNT(*) AS cnt
FROM InvoiceItems
GROUP BY invoiceid) AS T
ON I.invoiceid = T.invoiceid

--
David Portas
SQL Server MVP
--

Count of Invoices for Each Hour

This is actually a MySQL query, but I think it's generic enough that any SQL flavour should help.

I need to get the number of invoices per hour between 7am to 9pm and "the rest" (for each day of a week). I thought I knew what I was doing, but I'm getting the total transactions for the day placed in an hour's field (and not any particular hour, that I can tell)

Here's my query:

SELECT
DAYNAME(TransDt) As 'Day'
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN COUNT(InvNum) ELSE 0 END) as NumTrans7
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN COUNT(InvNum) ELSE 0 END) as NumTrans8
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 9 THEN COUNT(InvNum) ELSE 0 END) as NumTrans9
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 10 THEN COUNT(InvNum) ELSE 0 END) as NumTrans10
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 11 THEN COUNT(InvNum) ELSE 0 END) as NumTrans11
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 12 THEN COUNT(InvNum) ELSE 0 END) as NumTrans12
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 13 THEN COUNT(InvNum) ELSE 0 END) as NumTrans13
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 14 THEN COUNT(InvNum) ELSE 0 END) as NumTrans14
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 15 THEN COUNT(InvNum) ELSE 0 END) as NumTrans15
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 16 THEN COUNT(InvNum) ELSE 0 END) as NumTrans16
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 17 THEN COUNT(InvNum) ELSE 0 END) as NumTrans17
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 18 THEN COUNT(InvNum) ELSE 0 END) as NumTrans18
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 19 THEN COUNT(InvNum) ELSE 0 END) as NumTrans19
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 20 THEN COUNT(InvNum) ELSE 0 END) as NumTrans20
,(CASE WHEN DATE_FORMAT(TransDt, '%H') = 21 THEN COUNT(InvNum) ELSE 0 END) as NumTrans21
,(CASE WHEN (DATE_FORMAT(TransDt, '%H') < 7) OR (DATE_FORMAT(TransDt, '%H') > 21) THEN COUNT(InvNum) ELSE 0 END) as NumTransOther
,COUNT(InvNum) AS TotalTrans
FROM
tblTransactions
WHERE
(StoreNum = 123)
and (TransDt >= '2006-12-04 01:00:00')
and (TransDt <= '2006-12-11 00:59:59')
group by
DAYNAME(TransDt)
ORDER BY
TransDt;

What did I do wrong?

TIATry SUM() instead of COUNT():
SELECT
DAYNAME(TransDt) As 'Day'
,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 7 THEN 1 ELSE 0 END) as NumTrans7
,SUM(CASE WHEN DATE_FORMAT(TransDt, '%H') = 8 THEN 1 ELSE 0 END) as NumTrans8
,...etc...
:shocked:|||That was it. Thanks.

Count of different values

Hi all,
I have a table in which there is a column that can only contain two values: Male and Female.
How should I write a query that will give me a total of both Male and Female?
For example: Male: 23 Female:27
Thanks!
SanderHello Sander,

this is a SQL statement that give you the needed datas in two records

select MaleFemale, count(1) from statistic
group by MaleFemale

Is that good for you ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Count of Counts in SQL Server

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parulthat doesn't make any sense

a count of a count will always be 1|||why is that so? how do we get a count of all those rows that appear in the inner query?|||okay, let's break this problem down into steps

could you please run the inner query all by itself and show me what you get|||Since the inner select query has a group by field, the number of rows it returned may be more than one also.|||it shows you different counts by student first name; the result looks like:
count
--
5
24
2
23
2|||ah yes, okay, then i think what you want is this --select count(*)
from (
select count(*)
from student
group by firstName
having count(*) > 1
) as counts|||Exactly, but this is giving me the following error:

No column was specified for column 1 of 'counts'.|||I got it! The inner select column needs an alias.
select count(*)
from (
select count(*) as 'cnt'
from student
group by firstName
having count(*) > 1
) as counts

Thanks so much for your help!

Count need to bring back a zero when no matches

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.

This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d1

)d2

I'd be tempted to put your results into a temptable or table variable and add the value 0 if the insert adds 0 rows.

eg

Code Snippet

DECLARE @.Table TABLE (Iwia INT, Denominator FLOAT)

INSERT INTO @.Table

SELECT......

IF @.@.ROWCOUNT = 0

INSERT INTO @.Table
VALUES (0, 0)

SELECT Iwia, Denominator
FROM @.Table

HTH!

|||

Use ISNULL(column, 0) around your two arguments.

e.g.

Code Snippet

select d.lwia,

cast((ISNULL(d.closegoal, 0) + ISNULL(d1.opengoal, 0)) as float)denominator

...

|||

Try this

SELECT d.lwia

--, cast((d.closegoal + d1.opengoal) as float)denominator

, SUM(goal) as denominator

FROM (

SELECT yg.lwia

,0 as goal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

--==========

UNION ALL

--==========

SELECT yg.lwia

,IsNull(cast(count(yg.appid)as int),0) as goal-- closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

--==========

UNION ALL

--==========

-- Closure with open goal

select yg.lwia

, IsNull(cast(count(yg.appid)as int) as goal--opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d

GROUP BY d.lwia

Count need to bring back a zero when no matches

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.

This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d1

)d2

I'd be tempted to put your results into a temptable or table variable and add the value 0 if the insert adds 0 rows.

eg

Code Snippet

DECLARE @.Table TABLE (Iwia INT, Denominator FLOAT)

INSERT INTO @.Table

SELECT......

IF @.@.ROWCOUNT = 0

INSERT INTO @.Table
VALUES (0, 0)

SELECT Iwia, Denominator
FROM @.Table

HTH!

|||

Use ISNULL(column, 0) around your two arguments.

e.g.

Code Snippet

select d.lwia,

cast((ISNULL(d.closegoal, 0) + ISNULL(d1.opengoal, 0)) as float)denominator

...

|||

Try this

SELECT d.lwia

--, cast((d.closegoal + d1.opengoal) as float)denominator

, SUM(goal) as denominator

FROM (

SELECT yg.lwia

,0 as goal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

--==========

UNION ALL

--==========

SELECT yg.lwia

,IsNull(cast(count(yg.appid)as int),0) as goal-- closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @.RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @.EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

--==========

UNION ALL

--==========

-- Closure with open goal

select yg.lwia

, IsNull(cast(count(yg.appid)as int) as goal--opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @.BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @.EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @.RWB

group by yg.lwia

)d

GROUP BY d.lwia