CODEWITHSUNDEEP
sql
user243837
user243837

Reputation: 3

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

Upvotes: 0

Views: 59

Answers (2)

spencer7593
spencer7593

Reputation: 108370

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

Upvotes: 1

Hogan
Hogan

Reputation: 70513

Use a join and a Group By -- like this:

SELECT P.USERID, P.CREATION_DATE, COUNT(P.ID) AS COUNTID, COUNT(I.NUM) AS COUNTNUM
FROM PART P
JOIN IDENTITY I ON P.USERID = I.USERID AND P.CREATION_DATE = I.CREATION_DATE
WHERE P.USERID = $P{userId}  and P.CREATION_DATE = $P{creationDate}
GROUP BY P.USERID, P.CREATION_DATE

As a side bonus if you take out the WHERE you can see the results for all users and all dates.

Note, if not all users and dates are in the PART table or Identity table do this:

SELECT B.USERID, B.CREATION_DATE, COUNT(P.ID) AS COUNTID, COUNT(I.NUM) AS COUNTNUM
FROM (
   SELECT DISTINCT USERID, CREATEION_DATE FROM PART
    UNION 
   SELECT DISTINCT USERID, CREATEION_DATE FROM IDENTITY
) AS B
LEFT JOIN PART P ON B.USERID = P.USERID AND B.CREATION_DATE = P.CREATION_DATE
LEFT JOIN IDENTITY I ON B.USERID = I.USERID AND B.CREATION_DATE = I.CREATION_DATE
WHERE B.USERID = $P{userId}  and B.CREATION_DATE = $P{creationDate}
GROUP BY B.USERID, B.CREATION_DATE

NOTE: This second query is much more correct, but might not be needed depending on your data. Since you don't tell us anything about your data or data model it is hard for me to know if the first query will work.

Upvotes: 0

Related Questions