group by - 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

General SQL Server Forums

New to SQL Server Programming

group by

Author

Topic

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-16 : 13:04:49

I have a table as below

chapterid xmlfile
1234 123.xml
1234 123.xml
1234 123.xml
1234 123.xml
4567 123.xml
4567 123.xml
6789 145.xml
7890 234.xml
7890 234.xml
7890 234.xml

I would need an output that lists the distinct number of chapterids for each xmlfile
1) required output
chapterid xmlfile
1234 123.xml
4567 123.xml
6789 145.xml
7890 234.xml

I would also need an output that lists the xmlfile with more than one chapterid

2) required output
chapterid xmlfile
1234 123.xml
4567 123.xml
7890 234.xml

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-04-16 : 13:17:30

SELECT chapterid xmlfile
FROM TABLE
GROUP BY chapterid xmlfile

SELECT chapterid xmlfile
FROM TABLE
GROUP BY chapterid xmlfile
HAVING COUNT(chaptered) > 1

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-16 : 13:34:53

That is not giving the right results. The table will have duplicates as mentioned above. If there are rows with duplicates, Having count(chapterid) > 1 will consider as > 1 and give the result.
'Distinct' needs to be applied. I need the output as mentioned above.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-16 : 14:11:47

Change the HAVING clause to

HAVING COUNT(DISTINCT chapterid) > 1

You also need a few commas - e.g.

SELECT chapterid, xmlfile

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-16 : 17:15:24

I am not getting any output, no rows are returned

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-16 : 17:31:31

My suggestion about using distinct was incorrect. MichaelJSQL's query produces exactly the output you specified in your original posting. If you are not getting it, copy and paste this query and run it. Then compare with your actual data to see what might be different.

CREATE TABLE #test(chapterId INT, xmlfile VARCHAR(32));

INSERT INTO #test VALUES
(1234,'123.xml'),
(1234,'123.xml'),
(1234,'123.xml'),
(1234,'123.xml'),
(4567,'123.xml'),
(4567,'123.xml'),
(6789,'145.xml'),
(7890,'234.xml'),
(7890,'234.xml'),
(7890,'234.xml');

SELECT  chapterid ,
        xmlfile
FROM    #test
GROUP BY chapterid ,
        xmlfile;

SELECT  chapterId ,
        xmlfile
FROM    #test
GROUP BY chapterId ,
        xmlfile
HAVING  COUNT(chapterid) > 1;

DROP TABLE #test;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources