0

The query i wrote is: 

SELECT ST,COUNT(PHONE) FROM Consumer.Axciom092013 
WHERE MR_AMT >= (HOME_MKT_VALUE_(.45)) 
and ST = 'TX' or ST = 'CA' or ST = 'AZ' or ST = 'CO' or ST = 'FL';

Simply i am trying to find the LTV (Loan-To-Value) ratio of those states, but right now I only need the count of how many records of distinct phone numbers are in each of those states.

I keep getting error about incorrect syntax in line 1.
I thought i did a step by step for it, but cannot seem to get to work!

I have switched the query around and tried just running SELECT ST,COUNT(*) but then i get an error saying the function HOME_MKT_VALUE does not exist

Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
William Adomanis
  • 23
  • 2
  • 5

2 Answers2

1

I suspect that you want this:

SELECT ST, COUNT(distinct PHONE)
FROM Consumer.Axciom092013 
WHERE MR_AMT >= HOME_MKT_VALUE_ * 0.45 and
      ST IN ('TX', 'CA', 'AZ', 'CO', 'FL');

SQL only does multiplication with an explicit *. Your expression HOME_MKT_VALUE_(.45) is the syntax for a function call, not multiplication.

I added the distinct for phone because you say you want "distinct phone numbers", implying that there could be duplicates on different rows.

EDIT:

By the way, your original query actually has this logic in the where clause:

WHERE (MR_AMT >= HOME_MKT_VALUE_ * 0.45 and ST = 'TX')
      or ST IN ('CA', 'AZ', 'CO', 'FL');

If that is the logic you really do want, then use it instead.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

try this

SELECT     ST, COUNT(DISTINCT PHONE) 
FROM       Consumer.Axciom092013 
WHERE      MR_AMT >= (HOME_MKT_VALUE_(.45)) 
           and ST IN ('TX', 'CA', 'AZ', 'CO', 'FL')
GROUP BY   ST;                                    //mistaken , PHONE

EDIT: I did mistake should not be grouped by PHONE.

Iqbal
  • 1,266
  • 1
  • 17
  • 21
  • If you are getting the COUNT for phone records, why would you group on PHONE? – PinnyM Feb 02 '14 at 17:20
  • @PinnyM See your first answer, you were missing what he wanted. He wanted count of distinct phone numbers. I just wanted to group on phone as well so he can get what he wanted. – Iqbal Feb 02 '14 at 17:24
  • Are you sure this does what you think it does? Your query would seem to return a single row for each unique ST and PHONE (with a count of how many times that PHONE is duplicated). It appears that the OP wants a count of DISTINCT phone numbers (count of phone numbers with duplicates removed). – PinnyM Feb 02 '14 at 17:26
  • @PinnyM I am sure, it will group on ST under ST it will group on PHONE. – Iqbal Feb 02 '14 at 17:30
  • @PinnyM I already explained. Can you tell me, what a group on do? – Iqbal Feb 02 '14 at 17:36
  • My reading is the OP wants to *count distinct phone numbers* (`I only need the count of how many records of distinct phone numbers are in each of those states`), not to *count number of instances of every distinct phone number*. Your query seems to find an answer to the latter. – Andriy M Feb 02 '14 at 19:40
  • @AndriyM You are right about my query. There might be misunderstanding that what he wants. "in each of those states", so I grouped by ST and count them. – Iqbal Feb 02 '14 at 21:49