Count of rows in join Query - 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 2005 Forums

Transact-SQL (2005)

Count of rows in join Query

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-21 : 14:05:47

Dear All,
I am using the below query to get the all the field,here i need to get the coutnt of rows as a separate column as count how to get it.

Lets say below query return 6 rows the count column also should return the same 6.

SELECT T1."INVOICE_CREATION_PERIOD_IH" AS 'INVOICE CREATE PERIOD',
T1."OPEN_CLOSED_INVOICES_BAR" AS 'OPEN/CLOSED INVOICES',
T1."ORIGINAL_FSC_IH" AS 'FSC',
(DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) AS 'CHARGE LAG',
CASE WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) BETWEEN 0 AND 7))
THEN ('0 - 7 DAYS')
WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) > 91))
THEN ('> 91 DAYS')
ELSE (NULL)
END AS 'LAG DAYS',
T1."SERVICE_DATE_IH" AS 'SERVICE DATE IH',
T3."LOCATION_CLASS_ILR" AS 'LOCATION CLASS ILR',
T3."LOCATION_NAME_ILR" AS 'LOCATION NAME ILR',
--@@ROWCOUNT AS 'INVOICE COUNT',
--COUNT(T1.INVOICE_CREATION_PERIOD_IH) AS 'INVOICE COUNT',
SUM(T1."TOTAL_CHARGES_IH") AS 'TOTAL CHARGES IH'
FROM INVOICE_HEADER_IH T1
INNER JOIN PROVIDER_DIMENSION T2 ON T2."GROUP_NUMBER" = T1."GROUP_NUMBER_IH" AND T2."ID102" = T1."ID_102_IH"
AND T2."ID202" = T1."ID_202_IH" AND T2."ID3" = T1."ID_3_IH"
INNER JOIN IDX_LOCATION_ROLLUP_ILR T3 ON T1."ID_100_IH" = T3."ID_100_ILR"
WHERE T1."INVOICE_CREATION_PERIOD_IH" >= 200601

GROUP BY T1."INVOICE_CREATION_PERIOD_IH",T1."OPEN_CLOSED_INVOICES_BAR",T1."GROUP_NUMBER_IH",T2."PROVIDER_SK",
T1."ID_100_IH",T1."ORIGINAL_FSC_IH",T1."INVOICE_CREATION_DATE_IH",T1."SERVICE_DATE_IH",T3."LOCATION_CLASS_ILR",
T3."LOCATION_NAME_ILR"

thanks

ashw1984
Starting Member

6 Posts

Posted - 2009-09-21 : 14:23:11

try this
COUNT(*) AS 'INVOICE COUNT',

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-09-21 : 14:26:09

I tried this this its not retuning the exact rows..instead it is giving the wrong value as where condition is after this select

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-22 : 02:07:08

You cannot use the count() function here for getting the over all count, as it will give you the specific count for each set of rows as per your group by clause. You have to use it outside of your grouping clause like

SELECT COUNT (1) FROM
(
SELECT T1."INVOICE_CREATION_PERIOD_IH" AS 'INVOICE CREATE PERIOD',
T1."OPEN_CLOSED_INVOICES_BAR" AS 'OPEN/CLOSED INVOICES',
T1."ORIGINAL_FSC_IH" AS 'FSC',
(DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) AS 'CHARGE LAG',
CASE WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) BETWEEN 0 AND 7))
THEN ('0 - 7 DAYS')
WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) > 91))
THEN ('> 91 DAYS')
ELSE (NULL)
END AS 'LAG DAYS',
T1."SERVICE_DATE_IH" AS 'SERVICE DATE IH',
T3."LOCATION_CLASS_ILR" AS 'LOCATION CLASS ILR',
T3."LOCATION_NAME_ILR" AS 'LOCATION NAME ILR',
--@@ROWCOUNT AS 'INVOICE COUNT',
--COUNT(T1.INVOICE_CREATION_PERIOD_IH) AS 'INVOICE COUNT',
SUM(T1."TOTAL_CHARGES_IH") AS 'TOTAL CHARGES IH'
FROM INVOICE_HEADER_IH T1
INNER JOIN PROVIDER_DIMENSION T2 ON T2."GROUP_NUMBER" = T1."GROUP_NUMBER_IH" AND T2."ID102" = T1."ID_102_IH"
AND T2."ID202" = T1."ID_202_IH" AND T2."ID3" = T1."ID_3_IH"
INNER JOIN IDX_LOCATION_ROLLUP_ILR T3 ON T1."ID_100_IH" = T3."ID_100_ILR"
WHERE T1."INVOICE_CREATION_PERIOD_IH" >= 200601

GROUP BY T1."INVOICE_CREATION_PERIOD_IH",T1."OPEN_CLOSED_INVOICES_BAR",T1."GROUP_NUMBER_IH",T2."PROVIDER_SK",
T1."ID_100_IH",T1."ORIGINAL_FSC_IH",T1."INVOICE_CREATION_DATE_IH",T1."SERVICE_DATE_IH",T3."LOCATION_CLASS_ILR",
T3."LOCATION_NAME_ILR"
) A

There is no meaning of getting it as a an additional column in your main result set as the value will be same for all the rows. Still If you want to return it as an additional column in your result set you can cross join this part with your main query.

SELECT
T1."INVOICE_CREATION_PERIOD_IH" AS 'INVOICE CREATE PERIOD',
T1."OPEN_CLOSED_INVOICES_BAR" AS 'OPEN/CLOSED INVOICES',
T1."ORIGINAL_FSC_IH" AS 'FSC',
(DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) AS 'CHARGE LAG',
CASE WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) BETWEEN 0 AND 7))
THEN ('0 - 7 DAYS')
WHEN (((DATEPART(DD,(T1."INVOICE_CREATION_DATE_IH" - T1."SERVICE_DATE_IH"))) > 91))
THEN ('> 91 DAYS')
ELSE (NULL)
END AS 'LAG DAYS',
T1."SERVICE_DATE_IH" AS 'SERVICE DATE IH',
T3."LOCATION_CLASS_ILR" AS 'LOCATION CLASS ILR',
T3."LOCATION_NAME_ILR" AS 'LOCATION NAME ILR',
--@@ROWCOUNT AS 'INVOICE COUNT',
--COUNT(T1.INVOICE_CREATION_PERIOD_IH) AS 'INVOICE COUNT',
SUM(T1."TOTAL_CHARGES_IH") AS 'TOTAL CHARGES IH',
MAX(CNT.REC_COUNT) AS 'INVOICE COUNT'
FROM
INVOICE_HEADER_IH T1
INNER JOIN
PROVIDER_DIMENSION T2
ON
T2."GROUP_NUMBER" = T1."GROUP_NUMBER_IH" AND
T2."ID102" = T1."ID_102_IH" AND
T2."ID202" = T1."ID_202_IH" AND
T2."ID3" = T1."ID_3_IH"
INNER JOIN
IDX_LOCATION_ROLLUP_ILR T3
ON T1."ID_100_IH" = T3."ID_100_ILR"

CROSS JOIN
(
SELECT COUNT(1) AS REC_COUNT FROM
(
SELECT 'X'
FROM
INVOICE_HEADER_IH T1
INNER JOIN
PROVIDER_DIMENSION T2 ON
T2."GROUP_NUMBER" = T1."GROUP_NUMBER_IH" AND
T2."ID102" = T1."ID_102_IH" AND
T2."ID202" = T1."ID_202_IH" AND
T2."ID3" = T1."ID_3_IH"
INNER JOIN
IDX_LOCATION_ROLLUP_ILR T3 ON
T1."ID_100_IH" = T3."ID_100_ILR"
WHERE
T1."INVOICE_CREATION_PERIOD_IH" >= 200601
GROUP BY
T1."INVOICE_CREATION_PERIOD_IH",
T1."OPEN_CLOSED_INVOICES_BAR",
T1."GROUP_NUMBER_IH",
T2."PROVIDER_SK",
T1."ID_100_IH",
T1."ORIGINAL_FSC_IH",
T1."INVOICE_CREATION_DATE_IH",
T1."SERVICE_DATE_IH",
T3."LOCATION_CLASS_ILR",
T3."LOCATION_NAME_ILR"
)A
)CNT
WHERE T1."INVOICE_CREATION_PERIOD_IH" >= 200601
GROUP BY T1."INVOICE_CREATION_PERIOD_IH",T1."OPEN_CLOSED_INVOICES_BAR",T1."GROUP_NUMBER_IH",T2."PROVIDER_SK",
T1."ID_100_IH",T1."ORIGINAL_FSC_IH",T1."INVOICE_CREATION_DATE_IH",T1."SERVICE_DATE_IH",T3."LOCATION_CLASS_ILR",
T3."LOCATION_NAME_ILR"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources