September 24, 2010 at 9:03 am
I need to count the number of rows from a few joined tables. A normal SELECT COUNT(*) doesn't work. I think it's due to the use of CASE statements within the SELECT that are also doing COUNTS. Here is my query. How can I change it to get only 1 row back with the count of the records? I can't simply remove the CASE statements as they seem to affect what rows are returned.
SELECT
Cast(
CAST(YEAR(GETDATE() ) AS VARCHAR(4)) + '-' +
CAST(MONTH(GETDATE() ) -3 AS VARCHAR(2)) + '-' + '1' AS DATETIME) AS RUN_DT
,SOP_POOL_ID
,C622.CLNT_NB
,CASE
WHEN CALL_TYPE_CD = 'UM' THEN COUNT(CALL_TYPE_CD)
END AS CALL_TYPE_UM_CT
,CASE
WHEN CALL_TYPE_CD = 'MA' THEN COUNT(CALL_TYPE_CD)
END AS CALL_TYPE_MA_CT
,CASE
WHEN CALL_TYPE_CD = 'PD' THEN COUNT(CALL_TYPE_CD)
END AS CALL_TYPE_PD_CT
,CASE
WHEN CALL_TYPE_CD = 'PG' THEN COUNT(CALL_TYPE_CD)
END AS CALL_TYPE_PG_CT
,CASE
WHEN CALL_TYPE_CD = 'PD' OR CALL_TYPE_CD = 'PG' THEN COUNT(CALL_TYPE_CD)
END AS CALL_TYPE_PD_PG_CT
FROM T401_CALL_LOG_3MTH C401 (NOLOCK)
LEFT OUTER JOIN
dbo.T575C_ACCT_STC T575 (NOLOCK)
ON
T575.FACS_SYS_ACCT_NB = C401.FACS_SYS_ACCT_NB
AND T575.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.CLNT_PRTFL_GENL_620 C620 (NOLOCK)
ON
T575.CLNT_PRTFL_SK = C620.CLNT_PRTFL_GENL_SK
AND C620.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.T622_CLNT_POOL C622 (NOLOCK)
ON
C622.CLNT_NB = C620.CLNT_NB
AND C622.CURR_IN = 'Y'
WHERE
MONTH(C401.CALL_BEGAN_DT) = MONTH(GETDATE()) -3
AND YEAR(C401.CALL_BEGAN_DT) = YEAR(GETDATE())
AND T575.FACS_SYS_ACCT_NB IS NOT NULL
AND T575.CGNT_ACCT_NB IS NULL
AND C401.CGNT_ACCT_NB IS NULL
AND (POOL_NB Between 1 and 89
or POOL_NB Between 100 and 899
or POOL_NB Between 1000 and 1899
or POOL_NB Between 2000 and 4899
or POOL_NB Between 6000 and 8999)
GROUP BY
CALL_BEGAN_DT
,SOP_POOL_ID
,C622.CLNT_NB
,CALL_TYPE_CD
September 24, 2010 at 9:51 am
I'm able to get the correct record count with the sql below. However, I get 650xxx individual rows because of the Group By clause instead of 1 row with the 650xxx total. Any help in getting 1 row for the result set total count is much appreciated.
SELECT COUNT(*)
FROM T401_CALL_LOG_3MTH C401 (NOLOCK)
LEFT OUTER JOIN
dbo.T575C_ACCT_STC T575 (NOLOCK)
ON
T575.FACS_SYS_ACCT_NB = C401.FACS_SYS_ACCT_NB
AND T575.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.CLNT_PRTFL_GENL_620 C620 (NOLOCK)
ON
T575.CLNT_PRTFL_SK = C620.CLNT_PRTFL_GENL_SK
AND C620.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.T622_CLNT_POOL C622 (NOLOCK)
ON
C622.CLNT_NB = C620.CLNT_NB
AND C622.CURR_IN = 'Y'
WHERE
MONTH(C401.CALL_BEGAN_DT) = MONTH(GETDATE()) -3
AND YEAR(C401.CALL_BEGAN_DT) = YEAR(GETDATE())
AND T575.FACS_SYS_ACCT_NB IS NOT NULL
AND T575.CGNT_ACCT_NB IS NULL
AND C401.CGNT_ACCT_NB IS NULL
AND (POOL_NB Between 1 and 89
or POOL_NB Between 100 and 899
or POOL_NB Between 1000 and 1899
or POOL_NB Between 2000 and 4899
or POOL_NB Between 6000 and 8999)
GROUP BY
CALL_BEGAN_DT
SOP_POOL_ID
,C622.CLNT_NB
,CALL_TYPE_CD
September 24, 2010 at 11:40 am
Got it! I kept thinking there must be a way to do an overall COUNT of what the query was doing. Here it is:
SELECT COUNT(*) FROM (
SELECT CALL_BEGAN_DT
SOP_POOL_ID
,C622.CLNT_NB
,CALL_TYPE_CD
FROM T401_CALL_LOG_3MTH C401 (NOLOCK)
LEFT OUTER JOIN
dbo.T575C_ACCT_STC T575 (NOLOCK)
ON
T575.FACS_SYS_ACCT_NB = C401.FACS_SYS_ACCT_NB
AND T575.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.CLNT_PRTFL_GENL_620 C620 (NOLOCK)
ON
T575.CLNT_PRTFL_SK = C620.CLNT_PRTFL_GENL_SK
AND C620.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.T622_CLNT_POOL C622 (NOLOCK)
ON
C622.CLNT_NB = C620.CLNT_NB
AND C622.CURR_IN = 'Y'
WHERE
MONTH(C401.CALL_BEGAN_DT) = MONTH(GETDATE()) -3
AND YEAR(C401.CALL_BEGAN_DT) = YEAR(GETDATE())
AND T575.FACS_SYS_ACCT_NB IS NOT NULL
AND T575.CGNT_ACCT_NB IS NULL
AND C401.CGNT_ACCT_NB IS NULL
AND (POOL_NB Between 1 and 89
or POOL_NB Between 100 and 899
or POOL_NB Between 1000 and 1899
or POOL_NB Between 2000 and 4899
or POOL_NB Between 6000 and 8999)
GROUP BY
CALL_BEGAN_DT
,SOP_POOL_ID
,C622.CLNT_NB
,CALL_TYPE_CD ) AS COUNT
September 24, 2010 at 1:40 pm
If you just take off the group by clause from the version where did select count(*), it should work just fine.
SELECT COUNT(*)
FROM T401_CALL_LOG_3MTH C401 (NOLOCK)
LEFT OUTER JOIN
dbo.T575C_ACCT_STC T575 (NOLOCK)
ON
T575.FACS_SYS_ACCT_NB = C401.FACS_SYS_ACCT_NB
AND T575.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.CLNT_PRTFL_GENL_620 C620 (NOLOCK)
ON
T575.CLNT_PRTFL_SK = C620.CLNT_PRTFL_GENL_SK
AND C620.CURR_IN = 'Y'
LEFT OUTER JOIN
dbo.T622_CLNT_POOL C622 (NOLOCK)
ON
C622.CLNT_NB = C620.CLNT_NB
AND C622.CURR_IN = 'Y'
WHERE
MONTH(C401.CALL_BEGAN_DT) = MONTH(GETDATE()) -3
AND YEAR(C401.CALL_BEGAN_DT) = YEAR(GETDATE())
AND T575.FACS_SYS_ACCT_NB IS NOT NULL
AND T575.CGNT_ACCT_NB IS NULL
AND C401.CGNT_ACCT_NB IS NULL
AND (POOL_NB Between 1 and 89
or POOL_NB Between 100 and 899
or POOL_NB Between 1000 and 1899
or POOL_NB Between 2000 and 4899
or POOL_NB Between 6000 and 8999)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 24, 2010 at 4:03 pm
Also, keep in mind that you can do this:
SELECT
...
,SUM (CASE WHEN CALL_TYPE_CD = 'UM' THEN 1 ELSE 0 END) AS CALL_TYPE_UM_CT
,SUM (CASE WHEN CALL_TYPE_CD = 'MA' THEN 1 ELSE 0 END) AS CALL_TYPE_MA_CT
,SUM (CASE WHEN CALL_TYPE_CD = 'PD' ...
FROM ...
GROUP BY ...
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply