Solved

Syntax for SQL subquery

Posted on 2009-03-29
16
635 Views
Last Modified: 2012-05-06
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
Comment
Question by:daintysally
  • 6
  • 4
  • 3
  • +1
16 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24014005
Can you provide your entire query so that it can be fixed out
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24014026
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
 

Author Comment

by:daintysally
ID: 24014116
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
 

Author Comment

by:daintysally
ID: 24014167
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24014194
your query is not properly structured, Can you explain your requirement.
0
 

Author Comment

by:daintysally
ID: 24014207
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24014215
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24014222
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:daintysally
ID: 24014326
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24014394
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24014408
What is the relation between (sometable) and AUDIT_PROPERTIES?
0
 

Author Comment

by:daintysally
ID: 24014418
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24014460
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24014558
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24014599
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
 

Author Closing Comment

by:daintysally
ID: 31564090
Thanks!!!  This worked perfectly, thank you, thank you all for your help!!!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now