1

I have a DE that lists of all ads on site.

That data includes, maker, model and listing price.

What I am trying to do is query out the following information:

  • distinct model
  • maker
  • the min list price
  • the max list price

I can query out model and make with no problem to a new DE...and get 20,008 records:

select DISTINCT b.[Listing_Model__c], b.[Listing_Make__c]
From Web_Information__c_Salesforce as b

When I try to pull in the min and max values, it's not working. Here is what I have tried (I'm using the convert operator because the list price is set as a text not a numeric field):

select CONVERT(numeric,'MIN(b.Listing_Price__c)') as 'MinListPrice', CONVERT(numeric,'MAX(b.Listing_Price__c)') as 'MaxListPrice'
From "Web_Information__c_Salesforce" as b

INNER JOIN "FSBO-MakeModels" as c
on
b.[Listing_Make__c] = c.[Listing_Make__c] and
b.[Listing_Model__c] = c.[Listing_Model__c]

It's only giving me 1 record... What I want is the min and max listing prices for those 20,008 different Make/Models.

Thanks for any assistance!

  • Is the Listing_Price_c field a number value? or does it have special characters like commas and dollar signs? – Gortonington Sep 22 '17 at 14:40
  • it's not a number field right now, it's a text field - using MIN and MAX will give me a character count, and if I could get that to work right now I'd be happy, LOL – user2322059 Sep 22 '17 at 14:52
1

Issue: You are using aggregate functions that are running the MAX and MIN for Every Model and Make . This is is why it is returning a single value.

Solution: A possible way to go about this will be to order the rows in a sub query and select the top and bottom from it.

Try this:

**/*Query 1 -DE 1*/**
select DISTINCT b.[Listing_Model__c], b.[Listing_Make__c]
From Web_Information__c_Salesforce as b

/* Query 2 -DE 2 */

 SELECT


 [Listing_Model__c]

 [Listing_Make__c]

 ,Listing_Price__c as MinlistPrice

 FROM


( SELECT 

 b.[Listing_Model__c]

 ,b.[Listing_Make__c]

 ,b.[Listing_Price__c] 

 ,ROW_NUMBER()OVER( PARTITION BY b.[Listing_Model__c] ORDER BY CAST('b.Listing_Price__c' as INT) ASC  ) as row

 FROM 

 Web_Information__c_Salesforce b ) x

WHERE x.row = 1

/* Query 3 -DE 3 */

 SELECT
 Listing_Model__c]

 ,[Listing_Make__c]

 ,Listing_Price__c as MaxlistPrice

 FROM

( SELECT 

 b.[Listing_Model__c]

 ,b.[Listing_Make__c]

 ,b.[Listing_Price__c] 

 ,ROW_NUMBER()OVER( PARTITION BY b.[Listing_Model__c] ORDER BY CAST('b.Listing_Price__c' as INT) DESC  ) as row

 FROM 

 Web_Information__c_Salesforce b ) x

WHERE x.row = 1

/* Query 4 - DE 4 */

SELECT

a.[Listing_Model__c]
,a.[Listing_Make__c]
,MaxlistPrice
,MinlistPrice

FROM [DE 1] a INNER JOIN [DE 2] b ON  a.[Listing_Model__c] = b.[Listing_Model__c]
AND a.[Listing_Make__c] = b.[Listing_Make__c]

INNER JOIN [DE 3] c ON  a.[Listing_Model__c] = c.[Listing_Model__c]
AND a.[Listing_Make__c] = c.[Listing_Make__c]
| improve this answer | |
  • Comments are not for extended discussion; this conversation has been moved to chat. – Adrian Larson Sep 25 '17 at 18:53
  • Thanks for moving it to chat, I posted further comments in there. – user2322059 Sep 25 '17 at 19:57
  • @user2322059 updated – Samuel Dare Sep 25 '17 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.