user243837 user243837 - 1 month ago 5
SQL Question

Sql query correct syntax

I'm trying to return in a reporting service, the count of ID and NUM, that have the USERID AND CREATION_DATE entered by the user.
My aim is to get one row as a result, containing both counts. I'm getting the count correct however I'm being displayed with several rows as
a return. (which are the rows that have the parameters specified by the user). How can I get only one row containing only the fields COUNTID and COUNTNUM.
I'm using Microsoft sql server.

SELECT

(SELECT COUNT(ID)
FROM PART
WHERE USERID = $P{userId} and CREATION_DATE = $P{creationDate}) as COUNTID ,
(SELECT COUNT(NUM)
FROM IDENTITY
WHERE USERID = $P{userId} and CREATION_DATE = $P{creationDate}) as COUNTNUM
FROM
PART,
IDENTITY

Answer

If you only want to return one row, and each of your subqueries is returning the "count" you want, you could just remove the FROM clause from the outer query. Something like this:

  SELECT ( SELECT COUNT(p.ID)
             FROM PART p
            WHERE p.USERID        = $P{userId}
              AND p.CREATION_DATE = $P{creationDate}
         ) AS COUNTID
       , ( SELECT COUNT(i.NUM)
             FROM IDENTITY i
            WHERE i.USERID        = $P{userId}
              AND i.CREATION_DATE = $P{creationDate}
         ) AS COUNTNUM

Personally, I'd write the query a little differently. I'd use the subqueries as inline views cross joined in the FROM clause, with each of the inline views returning a single row. Like this:

  SELECT cp.countid
       , ci.countnum
    FROM ( SELECT COUNT(p.ID) AS countid
             FROM PART p
            WHERE p.USERID        = $P{userId}
              AND p.CREATION_DATE = $P{creationDate}
         ) cp
   CROSS
    JOIN ( SELECT COUNT(i.NUM) AS countnum
             FROM IDENTITY i
            WHERE i.USERID        = $P{userId}
              AND i.CREATION_DATE = $P{creationDate}
         ) ci
Comments