Creating a subquery which conflicts with the where clause...

Question

Post reply

Creating a subquery which conflicts with the where clause...

krishnamurali2489

Mr or Mrs. 500

Points: 517
More actions
September 12, 2023 at 12:11 pm

#4284926
Hi All,
I have created a table named issues where incidents are being tracked. Every record has a master indicator column ("Y" or "N")which denotes that its the master incident. If the same incident type is created by another user, then the Master Indicator will be set to N and the master_issue column will be set to the issue_id (Primary key) of the master issue.
Now I want to run a query which shows all the master incidents along with the count of child incidents for each master incident. But the query is not returning the child incident count. Please let me know how to handle this scenario in SQL. The query which i tried and the DDL is mentioned below.
```
select v.issue_id , v.category_id ,v.ldk_id, count( master_issue=v.issue_id and master_ind='N')  from issuev1 v
where v.master_ind = 'Y'
group by v.issue_id ,v.category_id ,v.ldk_id 

CREATE TABLE xit.issuev1 (
issue_id int4 NOT NULL DEFAULT nextval('xit.sequence_for_user'::regclass),
category_id int4 NOT NULL,
ldk_id int4 NOT NULL,
master_ind varchar NULL,
master_issue int4 NULL,
child_ind varchar NULL,
age int4 NOT NULL,
created_date timestamp NULL,
updated_date timestamp NULL,
severity int4 NULL,
username varchar NOT NULL,
category_type_id int4 NULL,
CONSTRAINT issue1_pkey PRIMARY KEY (issue_id)
);
```

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Sorry... second look at the code I posted said it was wrong and took it down.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

If you would provide just 10 rows of sample data, in consumable format, it would help a lot.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

I agree with Phil. Posting inserts to populate the test table with data will help a whole lot.

Also, the code you posted isn't from SQL Server but, since you posted in an SQL Server forum, this will hopefully work for you...

   WITH cteChildCount AS
(--==== Get a count of the childred for each master
 SELECT master_issue,child_count = COUNT(*)
   FROM xit.issuev1
  WHERE master_ind = 'N'
  GROUP BY master_issue
)--==== Display the masters and their respective child counts
 SELECT m.issue_id, m.category_id, m.ldk_id, c.child_count
   FROM xit.issuev1 m
   JOIN cteChildCount c ON c.master_issue = m.issue_id
  WHERE m.master_ind = 'Y'
;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 4

There are two potential solutions depending on whether the child records have the same category_id and ldk_id as the master record. (NOTE: this is where sample data would be REALLY helpful.)

CASE 1: Matching. Use a CASE expression instead of a WHERE clause.

select v.issue_id
     , v.category_id
     , v.ldk_id
/* use a CASE expression instead of WHERE clause  */
     , SUM( case when master_ind='N' THEN 1 ELSE 0 END) AS child_cnt
     , COUNT(*) AS total_cnt
from issuev1 v
/*  where v.master_ind = 'Y' -- removing criteria, because children don't qualify) */
group by v.issue_id ,v.category_id ,v.ldk_id

CASE 2: Not matching. A little more complicated than CASE 1. Adding MAX() expressions for category_id and ldk_id.

select v.issue_id
     , MAX(case when master_ind = 'Y' THEN v.category_id END) AS category_id
     , MAX(case when master_ind = 'Y' THEN v.ldk_id END AS ldk_id
/* use a CASE expression instead of WHERE clause  */
     , SUM( case when master_ind='N' THEN 1 ELSE 0 END) AS child_cnt
     , COUNT(*) AS total_cnt
from issuev1 v
/*  where v.master_ind = 'Y' -- removing criteria, because children don't qualify) */
group by v.issue_id  /*  Remove category_id and ldk_id from GROUP BY. */

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 5

Phil Parkin wrote:

If you would provide just 10 rows of sample data, in consumable format, it would help a lot.

Hi Phil,

Sorry , I have missed to add test data in my original post. Please check the attached test data.

Attachments:

You must be logged in to view attached files.

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 6

Jeff Moden wrote:

I agree with Phil. Posting inserts to populate the test table with data will help a whole lot.
Also, the code you posted isn't from SQL Server but, since you posted in an SQL Server forum, this will hopefully work for you...
   WITH cteChildCount AS
(--==== Get a count of the childred for each master
 SELECT master_issue,child_count = COUNT(*)
   FROM xit.issuev1
  WHERE master_ind = 'N'
  GROUP BY master_issue
)--==== Display the masters and their respective child counts
 SELECT m.issue_id, m.category_id, m.ldk_id, c.child_count
   FROM xit.issuev1 m
   JOIN cteChildCount c ON c.master_issue = m.issue_id
  WHERE m.master_ind = 'Y'
;

Hi Jeff,

Thanks for your suggestion. I have added the test data in my previous post.

I ran this query but I am getting an error saying ERROR: column "child_count" does not exist

Is child_count a function in SQL server?. Please clarify.

krishnamurali2489 Mr or Mrs. 500 Points: 517 More actions · Answer 7

drew.allen wrote:

There are two potential solutions depending on whether the child records have the same category_id and ldk_id as the master record. (NOTE: this is where sample data would be REALLY helpful.)

CASE 1: Matching. Use a CASE expression instead of a WHERE clause.

select v.issue_id
     , v.category_id
     , v.ldk_id
/* use a CASE expression instead of WHERE clause  */
     , SUM( case when master_ind='N' THEN 1 ELSE 0 END) AS child_cnt
     , COUNT(*) AS total_cnt
from issuev1 v
/*  where v.master_ind = 'Y' -- removing criteria, because children don't qualify) */
group by v.issue_id ,v.category_id ,v.ldk_id

CASE 2: Not matching. A little more complicated than CASE 1. Adding MAX() expressions for category_id and ldk_id.

select v.issue_id
     , MAX(case when master_ind = 'Y' THEN v.category_id END) AS category_id
     , MAX(case when master_ind = 'Y' THEN v.ldk_id END AS ldk_id
/* use a CASE expression instead of WHERE clause  */
     , SUM( case when master_ind='N' THEN 1 ELSE 0 END) AS child_cnt
     , COUNT(*) AS total_cnt
from issuev1 v
/*  where v.master_ind = 'Y' -- removing criteria, because children don't qualify) */
group by v.issue_id  /*  Remove category_id and ldk_id from GROUP BY. */

Drew

Hi Drew,

Thanks for your suggestions. I have posted sample data in my previous post.

As you mentioned, the child records will have the same category_id and ldk_id.

Considering I have three records in table, one master and two child, When I run your query it is returning all the three records . I just want one record which has four columns, Master Issue ID, Category ID , LDK ID and Count Of Child Records (in this case 2). I am expecting something like below in my result set. Please advise.

Master ID    Category ID                  LDK ID     CountOfChildRecords

123                 1                       7              2

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

krishnamurali2489 wrote:

Jeff Moden wrote:
I agree with Phil. Posting inserts to populate the test table with data will help a whole lot.
Also, the code you posted isn't from SQL Server but, since you posted in an SQL Server forum, this will hopefully work for you...
   WITH cteChildCount AS
(--==== Get a count of the childred for each master
 SELECT master_issue,child_count = COUNT(*)
   FROM xit.issuev1
  WHERE master_ind = 'N'
  GROUP BY master_issue
)--==== Display the masters and their respective child counts
 SELECT m.issue_id, m.category_id, m.ldk_id, c.child_count
   FROM xit.issuev1 m
   JOIN cteChildCount c ON c.master_issue = m.issue_id
  WHERE m.master_ind = 'Y'
;
Hi Jeff,
Thanks for your suggestion. I have added the test data in my previous post.
I ran this query but I am getting an error saying ERROR: column "child_count" does not exist
Is child_count a function in SQL server?. Please clarify.

No. child_count is an alias column name for the result of COUNT(*) in the CTE Since you're not using SQL Server (based on the INT4 datatypes you used), it may be that the RDBMS you're using doesn't allow CTEs (Common Table Expressions), which is similar to Oracle's "Subquery Refactoring" statement of "WITH".

So, tell us... which RDBMS are you actually using because SQL <> SQL (and I actually warned about that in my post :D)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76985 More actions · Answer 9

The problem is the issue_id. Just remove it from the GROUP BY. I used it in the GROUP BY, because it was in your original query.

Also, if your master_ind is NULL rather than an empty string--likely since that column appears to be integer-- you can just count that column rather than use the CASE expression.

Finally, Phil asked for the data IN CONSUMABLE FORMAT. A CSV file is NOT consumable format. Consumable format means a script to insert data into a (preferably temporary) table.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ed B Hall of Fame Points: 3666 More actions · Answer 10

Jeff Moden wrote:

Since you're not using SQL Server (based on the INT4 datatypes you used), it may be that the RDBMS you're using doesn't allow CTEs (Common Table Expressions), which is similar to Oracle's "Subquery Refactoring" statement of "WITH".
So, tell us... which RDBMS are you actually using because SQL <> SQL (and I actually warned about that in my post :D)

I wonder if it is as simple as changing child_count = COUNT(*) to COUNT(*) AS child_count.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 11

Ed B wrote:

Jeff Moden wrote:
Since you're not using SQL Server (based on the INT4 datatypes you used), it may be that the RDBMS you're using doesn't allow CTEs (Common Table Expressions), which is similar to Oracle's "Subquery Refactoring" statement of "WITH".
So, tell us... which RDBMS are you actually using because SQL <> SQL (and I actually warned about that in my post :D)
I wonder if it is as simple as changing child_count = COUNT(*) to COUNT(*) AS child_count.

GREAT thought, Ed. That could certainly be the issue here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)