March 22, 2006 at 9:57 am
Hello,
The following query gives me the number of how many rows in each group. However I also need to know the total number of groups that have more than 1 row in them. Can it be easily done?
Thanks a lot for your help.
SELECT COUNT(1)
FROM F4104
WHERE IVXRT = 'C'
GROUP BY IVAN8, IVITM, IVEXDJ,IVURCD
HAVING COUNT(*) > 1
March 22, 2006 at 10:17 am
Take a look at GROUPING and ROLLUP in BOL:
SELECT CASE WHEN GROUPING(IVAN8) = 1 THEN 'All' ELSE IVAN8 END AS [IVAN8],
CASE WHEN GROUPING(IVITM) = 1 THEN 'All' ELSE IVITM END AS [IVITM],
CASE WHEN GROUPING(IVEXDJ) = 1 THEN 'All' ELSE IVEXDJ END AS [IVEXDJ],
CASE WHEN GROUPING(IVURCD) = 1 THEN 'All' ELSE IVURCD END AS [IVURCD],
COUNT(1) AS [TheCount]
FROM F4104
WHERE IVXRT = 'C'
GROUP BY IVAN8, IVITM, IVEXDJ,IVURCD WITH ROLLUP --( or WITH CUBE to get more combinations of results)
HAVING COUNT(1) > 1
-Eddie
Eddie Wuerch
MCM: SQL
March 23, 2006 at 6:22 am
thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply