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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

gagani
Posting Yak Master

112 Posts

Posted - 2015-04-16 : 17:15:24
I am not getting any output, no rows are returned
Go to Top of Page

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;
Go to Top of Page
   

- Advertisement -