Help with Group by and Count - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Help with Group by and Count

Author

Topic

dantesfu
Starting Member

7 Posts

Posted - 2006-06-24 : 22:31:01

I want to show the count of Unique Books and Chapters edited by Date but I am having a hard time getting the book count not to be counted more than ones but my query is not working.

Can you please tell me what I am doing wrong? Thank you!

SELECT DATE, COUNT(BookTitle) AS [BooksEdited], COUNT(Chapter) AS [ChaptersEdited]
FROM BooksEdited
GROUP BY DATE

Desired Output

Count of Books and Chapters Edited grouped by Date

Date ---- BooksEdited --- ChaptersEdited
4/25/2006 --- ---3 --- --- 5
4/26/2006 --- --- 2 --- --- 4
4/27/2006 --- --- 1 --- --- 3

Here is my data. Table: BooksEdited

Date --- --- BookTitle --- --- --- Chapter
4/25/2006 --- Crime and Punishment --- one
4/25/2006 --- Crime and Punishment --- two
4/25/2006 --- The Eastern Code --- one
4/25/2006 --- The Eastern Code --- two
4/25/2006 --- True Justice --- one
4/26/2006 --- White Lies --- one
4/26/2006 --- White Lies --- two
4/26/2006 --- The Rain Maker --- one
4/26/2006 --- The Rain Maker --- two
4/27/2006 --- Cold War --- one
4/27/2006 --- Cold War --- two
4/27/2006 --- Cold War --- three

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-24 : 22:42:29

[code]
select
DATE,
count(distinct BookTitle) as [BooksEdited],
count(distinct Chapter) as [ChaptersEdited]
from
BooksEdited
group by
DATE
order by
DATE

[/code]

CODO ERGO SUM

suneil23
Starting Member

4 Posts

Posted - 2006-06-25 : 01:16:24

Mr.Micheal ur solution to the question is incorrect...i tried ur answer but its showing like dis.

DATE BOOKSEDITED CHAPTERSEDITED
2006-04-25 00:00:00.000 4 2
2006-04-26 00:00:00.000 2 2
2006-04-27 00:00:00.000 2 3

UR SOLUTION IS TOTALLY ABSURD...PLS CHECK ME AND LETME KMNOW ASAP...

THANX*REGS
SQL Novice

suneil23
Starting Member

4 Posts

Posted - 2006-06-25 : 01:30:40

Dear Dantesfu please try dis pal..it surely gives the answer wat u need...

select DATE,count(distinct BookTitle) as [BooksEdited],count(Chapter) as [ChaptersEdited] from BooksEdited
group by date
order by date;

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-25 : 11:20:12

suneil23 --

First off, your "new" solution is exactly the same as what MVJ wrote (you know, the "TOTALLY ABSURD" one). Second, this isn't an AOL chatroom, so please try to act like an adult.

EDIT: see below.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-06-25 : 13:22:58

Sorry, but they aren't the same, Jeff.
MVJ had a distinct "Chapter" count, the other one didn't.

We haven't been told that (Date, BookTitle, Chapter) is a candidate key on BooksEdited. If it isn't, neither is right.


SELECT Date, COUNT(*) AS BooksEdited, SUM(ct_chapter) AS ChaptersEdited
FROM (
    SELECT Date, BookTitle, COUNT(DISTINCT Chapter) AS ct_chapter
    FROM BooksEdited
    GROUP BY Date, BookTitle
  ) AS A
GROUP BY Date

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-06-25 : 14:10:17

Thanks, Arnold -- I missed that. my fault. Point #2 still holds, though. :)

dantesfu
Starting Member

7 Posts

Posted - 2006-06-25 : 14:36:44

Thank you all Michael Valentine Jones, suneil23, Arnold Fribble. It worked!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources