CODEWITHSUNDEEP
sqlsql-servercount
Michele
Michele

Reputation: 21

Query with count

i have a problem with a query. I use T-SQL.

I have write this query:

SELECT  COUNT(*),
        MR.IDEsercizioRiabilitativo
FROM    AA_V_MONITOR_RisultatiEserciziR MR
        INNER JOIN AA_V_TER_TerapieRiabilitative TT
            ON MR.IDTerapia = TT.ID
WHERE   MR.IDEsercizioRiabilitativo IN ( 9, 10, 14 )
        AND TT.CodiceFiscaleAssistito IN ('RICCARDINOFUFFOL')
        AND MR.DATAESECUZIONE >= '2012-01-01'
        AND MR.DataEsecuzione <= '2013-12-12'
GROUP   BY MR.IDEsercizioRiabilitativo

i have this result:

count(*)    IdEsercizioRiabilitativo
2            10
1            11
1            14

This result it's ok but i would like the number of row. I would like to retrieve the number of IDESERCIZIORIABILITATIVO.

I would like to count the number of IdEsercizioRiabilitativo.

In the sample i should to retrieve a number 3.

IdEsercizioRiabilitativo 10,11,14. There are three distinct IdEsercizioRiabilitativo

I don't use a Java,C++ or other but i develop a StoreProceedure

so i have modify my query in

SELECT count()
FROM (
    SELECT COUNT()
        ,MR.IDEsercizioRiabilitativo
    FROM AA_V_MONITOR_RisultatiEserciziR MR
    INNER JOIN AA_V_TER_TerapieRiabilitative TT ON MR.IDTerapia = TT.ID
    WHERE MR.IDEsercizioRiabilitativo IN (
            9
            ,10
            ,14
            )
        AND TT.CodiceFiscaleAssistito IN ('RICCARDINOFUFFOL')
        AND MR.DATAESECUZIONE >= '2012-01-01'
        AND MR.DataEsecuzione <= '2013-12-12'
    GROUP BY MR.IDEsercizioRiabilitativo
    )

but i have this response

Msg 102, Level 15, State 1, Line 8 Sintassi non corretta in prossimità di ')'.

You can help me?

Thanks to all

Upvotes: 0

Views: 109

Answers (4)

Steve Ford
Steve Ford

Reputation: 7763

I think this might be simpler:

SELECT  COUNT(DISTINCT MR.IDEsercizioRiabilitativo)
FROM    AA_V_MONITOR_RisultatiEserciziR MR
        INNER JOIN AA_V_TER_TerapieRiabilitative TT
            ON MR.IDTerapia = TT.ID
WHERE   MR.IDEsercizioRiabilitativo IN ( 9, 10, 14 )
        AND TT.CodiceFiscaleAssistito IN ('RICCARDINOFUFFOL')
        AND MR.DATAESECUZIONE >= '2012-01-01'
        AND MR.DataEsecuzione <= '2013-12-12'

Upvotes: 0

Irvin Dominin
Irvin Dominin

Reputation: 30993

Try this:

SELECT count(*)
FROM (
    SELECT COUNT(*) as TOT
        ,MR.IDEsercizioRiabilitativo
    FROM AA_V_MONITOR_RisultatiEserciziR MR
    INNER JOIN AA_V_TER_TerapieRiabilitative TT ON MR.IDTerapia = TT.ID
    WHERE MR.IDEsercizioRiabilitativo IN (
            9
            ,10
            ,14
            )
        AND TT.CodiceFiscaleAssistito IN ('RICCARDINOFUFFOL')
        AND MR.DATAESECUZIONE >= '2012-01-01'
        AND MR.DataEsecuzione <= '2013-12-12'
    GROUP BY MR.IDEsercizioRiabilitativo
    ) temp

Upvotes: 0

Abhijith Nagarajan
Abhijith Nagarajan

Reputation: 4030

Use @@ROWCOUNT attribute to know what is the number of rows retrieved from the query.

If you are using Java JDBC api.. then you have resultSet.last() and resultSet.getRowNum(). These two methods are subjected to support by jdbc drivers and underlying database

Upvotes: 1

vaan38
vaan38

Reputation: 47

What if you use an other COUNT()

select count(*) from ((SELECT COUNT(*),MR.IDEsercizioRiabilitativo FROM FOO) as derivedTable)

Of course you have to change the derivedTable request, or just do another request and use a DISTINCT clause.

Upvotes: 0

Related Questions