[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

Syntax for SQL subquery

Hi,

I have written a query in sql that is pulling data from 3 tables. I need to create an additional column that will get a count of records in another column when the value = 'red' (fox example).  I should know how to do this, but for some reason, my subquery within my select statement is not returning the output that I am looking for.  See a snippet of my code below.
SELECT
STYLE,
(select(count(LOCATION.new_skirt)) from LOCATION
where LOCATION_COLOR = 'RED')as "red skit count",
PRODUCT_ID = STORE.PRODUCT_ID,
SIZE = STORE.SHIRT_SIZE,

Open in new window

0
daintysally
Asked:
daintysally
  • 6
  • 4
  • 3
  • +1
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Can you provide your entire query so that it can be fixed out
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it's presumably like this:
SELECT
STYLE,
(select(count(*)) from LOCATION l where l.LOCATION_COLOR = STORE.<COLOR_FIELD> )as "red skit count",
PRODUCT_ID = STORE.PRODUCT_ID,
SIZE = STORE.SHIRT_SIZE,

Open in new window

0
 
daintysallyAuthor Commented:
attached is a snippet of the real query that I am writing.  
SELECT
count([ap2.ap_new_value])AS CHANGE_HANDS from (select AP_FIELD_NAME FROM AUDIT_PROPERTIES AP2 where AP_FIELD_NAME = 'BG_Responsible'),
DEFECT_ID = BUG.BG_BUG_ID,
PCV = ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL'),
field,
field,
field
where (criteria)
group by (fields)
order by (fields)

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
daintysallyAuthor Commented:
this is the error message that I get when I try to run my query:

Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
your query is not properly structured, Can you explain your requirement.
0
 
daintysallyAuthor Commented:
why is my query not properly structured?  I'm trying to create a field by counting the values in another field where the value in that field is = 'BG_RESPONSIBLE'.  My query returns thousands of records and works fine with the exception of row 2 (which is the column I'm trying to create by counting).  I want to see a number where the field_name column is = BG_RESPONSIBLE.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this syntax should work:
SELECT
( SELECT count([ap2.ap_new_value]) 
    from FROM AUDIT_PROPERTIES AP2 
   where ap2.AP_FIELD_NAME = 'BG_Responsible')
) AS CHANGE_HANDS 
, DEFECT_ID = BUG.BG_BUG_ID,
PCV = ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL'),
field,
field,
field
from (sometable)
where (criteria)
group by (fields)
order by (fields)

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Why I meant unstructured is because:

1. You don't have FROM clause in your query before the where (criteria).
2. You have the subquery which you mentioned without proper braces.
3. ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL') is not a required operation.
4. select AP_FIELD_NAME FROM AUDIT_PROPERTIES AP2 where AP_FIELD_NAME = 'BG_Responsible' will always yield AP_FIELD_NAME as 'BG_Responsible' and hence it is not required.
0
 
daintysallyAuthor Commented:
Here is the modified version of the query.  It works, but the column returns a total count of the subquery in every row (over 9000), when I only need to see a '1' when ap_field_name = 'BG_Responsible'
SELECT
(select count([AP_NEW_VALUE])from AUDIT_PROPERTIES AP2 where AP2.AP_FIELD_NAME = 'BG_Responsible') AS CHANGE_HANDS,
DEFECT_ID = BUG.BG_BUG_ID,
PCV = ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL'),
field,
field,
field
from (sometable)
where (criteria)
group by (fields)
order by (fields)

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please understand that by letting us in the dark in regards to the rest of the query, aka how your subquery table is related to the main table, we cannot help.
0
 
Anthony PerkinsCommented:
What is the relation between (sometable) and AUDIT_PROPERTIES?
0
 
daintysallyAuthor Commented:
ok, you've twisted my arm.... :) here is the entire query

I need the count to include a report for distinct defect id's.
SELECT
(select count([AP_NEW_VALUE])from AUDIT_PROPERTIES AP2 where AP2.AP_FIELD_NAME = 'BG_Responsible') AS CHANGE_HANDS,
DEFECT_ID = BUG.BG_BUG_ID,
PLANNED_CLOSING_VER = ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL'),
STATUS = BUG.BG_STATUS,
SEVERITY = BUG.BG_SEVERITY,
SEC_STATUS = ISNULL(BUG.BG_USER_14,'NULL'),
ASSIGNED_TO = BUG.BG_RESPONSIBLE,
CIO_GROUP = ISNULL(BUG.BG_USER_62,'NULL'),
DETECTED_ON_DT = CONVERT(VARCHAR(10),BUG.BG_DETECTION_DATE,101),
REOPEN_DATE = ISNULL(BUG.BG_USER_18,'NULL'),
ROOT_CAUSE_DATE = ISNULL(BUG.BG_USER_57,'NULL'),
CLOSING_DATE = CONVERT(VARCHAR(10),BUG.BG_CLOSING_DATE,101),
ACTUAL_FIXED_DATE = CONVERT(VARCHAR(10),BUG.BG_USER_04,101), 
ISNULL(BUG.BG_USER_07,'NULL')as "TEST PHASE",
CHANGER = ISNULL(AL1.AU_USER,'NULL'),
CHANGE_DATE = ISNULL(CONVERT(VARCHAR(10),AL1.AU_TIME,101),'NULL'),
OLD_VALUE = ISNULL(AP1.AP_OLD_VALUE,'NULL'),
NEW_VALUE = ISNULL(AP2.AP_NEW_VALUE,'NULL'),
AGING =(SELECT DATEDIFF(dw,BG_DETECTION_DATE,GETDATE())-(DATEDIFF(wk,BG_DETECTION_DATE,GETDATE())*2) AS AGING), 
FIELDNAME = ISNULL(AP2.AP_FIELD_NAME, 'NULL'),
DOLLAR_AMOUNT = BUG.BG_USER_71,
NUM_OF_RECORDS = BUG.BG_USER_72,
BASEL_DATA_ELEMENT = BUG.BG_USER_75,
BASEL_DATA_ELEMENT_VALUES = BUG.BG_USER_76
from AUDIT_LOG AL1,AUDIT_PROPERTIES AP1,AUDIT_PROPERTIES AP2,BUG
where 
AL1.AU_ENTITY_ID = CAST(BUG.BG_BUG_ID AS VARCHAR(200))AND
AL1.AU_ACTION_ID = AP1.AP_ACTION_ID AND
AL1.AU_ACTION_ID = AP2.AP_ACTION_ID AND
AP1.AP_FIELD_NAME = AP2.AP_FIELD_NAME AND
AP2.AP_FIELD_NAME in ('bg_status', 'bg_responsible','BG_USER_14') AND
BUG.BG_USER_09 = 'Defect'AND
BUG.BG_USER_53 <> 'No'AND
BUG.BG_USER_55 <> 'Yes'AND
BUG.BG_USER_06 NOT IN ('CRDS','DQS')AND
BUG.BG_PROJECT NOT IN ('A Test SOR','AQ Forecasting','ECR ECRIS Back End', 'ECR_Front End Portal')AND
BUG.BG_USER_07 IN ('Prod Emulation 1','Prod Emulation 10','Prod Emulation 11','Prod Emulation 12','Prod Emulation 2','Prod Emulation 3',
                   'Prod Emulation 4','Prod Emulation 5','Prod Emulation 6','Prod Emulation 7','Prod Emulation 8','Prod Emulation 9',
                   'SIT - Refresh 1','SIT - Refresh1','SIT - Refresh 2','SIT - Refresh 3','SIT - Refresh 4','SIT - Refresh 5','SIT - Refresh 6',
                   'SIT - Refresh 7','SIT - Refresh 7a','SIT - Refresh 8','SIT - Refresh 9','SIT - Refresh 10','UAT - Refresh 1','UAT - Refresh 2',
                   'UAT- Refresh 3','UAT- Refresh 4','UAT- Refresh 5','UAT- Refresh 6','UAT- Refresh 7','UAT - Refresh 7a','UAT - Refresh 8',
                   'UAT - Refresh 9','UAT - Refresh 10','SOR SIT','SOR UAT') 
GROUP BY BG_USER_07,
         BG_USER_71,
         BG_USER_72,
         BG_USER_75,
         BG_USER_76,
         BUG.BG_BUG_ID,
	     BUG.BG_PLANNED_CLOSING_VER,BG_STATUS,BG_SEVERITY,BUG.BG_USER_14,BUG.BG_RESPONSIBLE,BG_USER_62,
		 BUG.BG_DETECTION_DATE,BG_USER_18,BG_USER_57,BUG.BG_CLOSING_DATE,BUG.BG_USER_04,
         AL1.AU_USER,AL1.AU_TIME,AP1.AP_OLD_VALUE,
         AP2.AP_NEW_VALUE,AP2.AP_FIELD_NAME
Order By BUG.BG_BUG_ID,AL1.AU_TIME

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Try this one out:
SELECT
(select count(DISTINCT [AP_NEW_VALUE])from AUDIT_PROPERTIES AP3 where AP2.AP_FIELD_NAME = AP3.AP_FIELD_NAME AND AP2.AP_FIELD_NAME = 'BG_Responsible') AS CHANGE_HANDS,
DEFECT_ID = BUG.BG_BUG_ID,
PLANNED_CLOSING_VER = ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL'),
STATUS = BUG.BG_STATUS,
SEVERITY = BUG.BG_SEVERITY,
SEC_STATUS = ISNULL(BUG.BG_USER_14,'NULL'),
ASSIGNED_TO = BUG.BG_RESPONSIBLE,
CIO_GROUP = ISNULL(BUG.BG_USER_62,'NULL'),
DETECTED_ON_DT = CONVERT(VARCHAR(10),BUG.BG_DETECTION_DATE,101),
REOPEN_DATE = ISNULL(BUG.BG_USER_18,'NULL'),
ROOT_CAUSE_DATE = ISNULL(BUG.BG_USER_57,'NULL'),
CLOSING_DATE = CONVERT(VARCHAR(10),BUG.BG_CLOSING_DATE,101),
ACTUAL_FIXED_DATE = CONVERT(VARCHAR(10),BUG.BG_USER_04,101), 
ISNULL(BUG.BG_USER_07,'NULL')as "TEST PHASE",
CHANGER = ISNULL(AL1.AU_USER,'NULL'),
CHANGE_DATE = ISNULL(CONVERT(VARCHAR(10),AL1.AU_TIME,101),'NULL'),
OLD_VALUE = ISNULL(AP1.AP_OLD_VALUE,'NULL'),
NEW_VALUE = ISNULL(AP2.AP_NEW_VALUE,'NULL'),
AGING =(SELECT DATEDIFF(dw,BG_DETECTION_DATE,GETDATE())-(DATEDIFF(wk,BG_DETECTION_DATE,GETDATE())*2) AS AGING), 
FIELDNAME = ISNULL(AP2.AP_FIELD_NAME, 'NULL'),
DOLLAR_AMOUNT = BUG.BG_USER_71,
NUM_OF_RECORDS = BUG.BG_USER_72,
BASEL_DATA_ELEMENT = BUG.BG_USER_75,
BASEL_DATA_ELEMENT_VALUES = BUG.BG_USER_76
from AUDIT_LOG AL1,AUDIT_PROPERTIES AP1,AUDIT_PROPERTIES AP2,BUG
where 
AL1.AU_ENTITY_ID = CAST(BUG.BG_BUG_ID AS VARCHAR(200))AND
AL1.AU_ACTION_ID = AP1.AP_ACTION_ID AND
AL1.AU_ACTION_ID = AP2.AP_ACTION_ID AND
AP1.AP_FIELD_NAME = AP2.AP_FIELD_NAME AND
AP2.AP_FIELD_NAME in ('bg_status', 'bg_responsible','BG_USER_14') AND
BUG.BG_USER_09 = 'Defect'AND
BUG.BG_USER_53 <> 'No'AND
BUG.BG_USER_55 <> 'Yes'AND
BUG.BG_USER_06 NOT IN ('CRDS','DQS')AND
BUG.BG_PROJECT NOT IN ('A Test SOR','AQ Forecasting','ECR ECRIS Back End', 'ECR_Front End Portal')AND
BUG.BG_USER_07 IN ('Prod Emulation 1','Prod Emulation 10','Prod Emulation 11','Prod Emulation 12','Prod Emulation 2','Prod Emulation 3',
                   'Prod Emulation 4','Prod Emulation 5','Prod Emulation 6','Prod Emulation 7','Prod Emulation 8','Prod Emulation 9',
                   'SIT - Refresh 1','SIT - Refresh1','SIT - Refresh 2','SIT - Refresh 3','SIT - Refresh 4','SIT - Refresh 5','SIT - Refresh 6',
                   'SIT - Refresh 7','SIT - Refresh 7a','SIT - Refresh 8','SIT - Refresh 9','SIT - Refresh 10','UAT - Refresh 1','UAT - Refresh 2',
                   'UAT- Refresh 3','UAT- Refresh 4','UAT- Refresh 5','UAT- Refresh 6','UAT- Refresh 7','UAT - Refresh 7a','UAT - Refresh 8',
                   'UAT - Refresh 9','UAT - Refresh 10','SOR SIT','SOR UAT') 
GROUP BY BG_USER_07,
         BG_USER_71,
         BG_USER_72,
         BG_USER_75,
         BG_USER_76,
         BUG.BG_BUG_ID,
             BUG.BG_PLANNED_CLOSING_VER,BG_STATUS,BG_SEVERITY,BUG.BG_USER_14,BUG.BG_RESPONSIBLE,BG_USER_62,
                 BUG.BG_DETECTION_DATE,BG_USER_18,BG_USER_57,BUG.BG_CLOSING_DATE,BUG.BG_USER_04,
         AL1.AU_USER,AL1.AU_TIME,AP1.AP_OLD_VALUE,
         AP2.AP_NEW_VALUE,AP2.AP_FIELD_NAME
Order By BUG.BG_BUG_ID,AL1.AU_TIME

Open in new window

0
 
Anthony PerkinsCommented:
Something like this perhaps:
SELECT	SUM(
		CASE AP2.AP_FIELD_NAME
			WHEN  'BG_Responsible' THEN 1
			ELSE 0
		END) CHANGE_HANDS,
		BUG.BG_BUG_ID DEFECT_ID,
		ISNULL(BUG.BG_PLANNED_CLOSING_VER,'NULL') PLANNED_CLOSING_VER,
		BUG.BG_STATUS STATUS,
		BUG.BG_SEVERITY SEVERITY,
		ISNULL(BUG.BG_USER_14,'NULL') SEC_STATUS,
		BUG.BG_RESPONSIBLE ASSIGNED_TO,
		ISNULL(BUG.BG_USER_62,'NULL') CIO_GROUP ,
		CONVERT(VARCHAR(10),BUG.BG_DETECTION_DATE,101) DETECTED_ON_DT,
		ISNULL(BUG.BG_USER_18,'NULL') REOPEN_DATE,
		ISNULL(BUG.BG_USER_57,'NULL') ROOT_CAUSE_DATE,
		CONVERT(VARCHAR(10),BUG.BG_CLOSING_DATE,101) CLOSING_DATE,
		CONVERT(VARCHAR(10),BUG.BG_USER_04,101) ACTUAL_FIXED_DATE, 
		ISNULL(BUG.BG_USER_07,'NULL') [TEST PHASE],
		ISNULL(AL1.AU_USER,'NULL') CHANGER,
		ISNULL(CONVERT(VARCHAR(10), AL1.AU_TIME, 101),'NULL') CHANGE_DATE,
		ISNULL(AP1.AP_OLD_VALUE,'NULL') OLD_VALUE,
		ISNULL(AP2.AP_NEW_VALUE,'NULL') NEW_VALUE,
		DATEDIFF(dw,BG_DETECTION_DATE,GETDATE()) - (DATEDIFF(wk,BG_DETECTION_DATE,GETDATE()) * 2) AGING, 
		ISNULL(AP2.AP_FIELD_NAME, 'NULL') FIELDNAME,
		BUG.BG_USER_71 DOLLAR_AMOUNT,
		BUG.BG_USER_72 NUM_OF_RECORDS,
		BUG.BG_USER_75 BASEL_DATA_ELEMENT,
		BUG.BG_USER_76 BASEL_DATA_ELEMENT_VALUES
from	AUDIT_LOG AL1
		Inner Join AUDIT_PROPERTIES AP1 On AL1.AU_ACTION_ID = AP1.AP_ACTION_ID
		Inner Join AUDIT_PROPERTIES AP2 On AL1.AU_ACTION_ID = AP2.AP_ACTION_ID And AP1.AP_FIELD_NAME = AP2.AP_FIELD_NAME
		Inner Join BUG On AL1.AU_ENTITY_ID = CAST(BUG.BG_BUG_ID AS VARCHAR(200))
where	AP2.AP_FIELD_NAME In ('bg_status', 'bg_responsible', 'BG_USER_14') 
		AND	BUG.BG_USER_09 = 'Defect'
		AND	BUG.BG_USER_53 <> 'No'
		AND	BUG.BG_USER_55 <> 'Yes'
		AND	BUG.BG_USER_06 NOT IN ('CRDS','DQS')
		AND	BUG.BG_PROJECT NOT IN ('A Test SOR','AQ Forecasting','ECR ECRIS Back End', 'ECR_Front End Portal')
		AND BUG.BG_USER_07 IN ('Prod Emulation 1','Prod Emulation 10','Prod Emulation 11','Prod Emulation 12','Prod Emulation 2','Prod Emulation 3',
                   'Prod Emulation 4','Prod Emulation 5','Prod Emulation 6','Prod Emulation 7','Prod Emulation 8','Prod Emulation 9',
                   'SIT - Refresh 1','SIT - Refresh1','SIT - Refresh 2','SIT - Refresh 3','SIT - Refresh 4','SIT - Refresh 5','SIT - Refresh 6',
                   'SIT - Refresh 7','SIT - Refresh 7a','SIT - Refresh 8','SIT - Refresh 9','SIT - Refresh 10','UAT - Refresh 1','UAT - Refresh 2',
                   'UAT- Refresh 3','UAT- Refresh 4','UAT- Refresh 5','UAT- Refresh 6','UAT- Refresh 7','UAT - Refresh 7a','UAT - Refresh 8',
                   'UAT - Refresh 9','UAT - Refresh 10','SOR SIT','SOR UAT') 
GROUP BY
		BUG.BG_USER_07,
		BUG.BG_USER_71,
		BUG.BG_USER_72,
		BUG.BG_USER_75,
		BUG.BG_USER_76,
		BUG.BG_BUG_ID,
		BUG.BG_PLANNED_CLOSING_VER,
		BUG.BG_STATUS,
		BUG.BG_SEVERITY,
		BUG.BG_USER_14,
		BUG.BG_RESPONSIBLE,
		BUG.BG_USER_62,
		BUG.BG_DETECTION_DATE,
		BG_USER_18,
		BG_USER_57,
		BUG.BG_CLOSING_DATE,
		BUG.BG_USER_04,
		AL1.AU_USER,
		AL1.AU_TIME,
		AP1.AP_OLD_VALUE,
		AP2.AP_NEW_VALUE,
		AP2.AP_FIELD_NAME
Order By 
		BUG.BG_BUG_ID,
		AL1.AU_TIME

Open in new window

0
 
Anthony PerkinsCommented:
Also and unrelated this:
ISNULL(AP2.AP_FIELD_NAME, 'NULL') FIELDNAME,

Can be changed to the simpler:
AP2.AP_FIELD_NAME FIELDNAME,

As from the WHERE clause it will never be Null.
0
 
daintysallyAuthor Commented:
Thanks!!!  This worked perfectly, thank you, thank you all for your help!!!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now