CODEWITHSUNDEEP
sqlsql-server
RIP JUICE
RIP JUICE

Reputation: 3

mySQL. How can I reuse the code in this instance without duplicating code?

That was a really badly worded question, but basically when i'm doing:

SELECT 
  SUM(CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END) AS coincidence, 
  COUNT(streetNumber) Amount, 
  Amount-concidence diff
FROM records
WHERE coincidence < Amount;

But obviously I cant do AMOUNT-COINCIDENCE, so how can I fix this without duplicating the code and having to do this instead:

SELECT
  SUM(CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END) AS coincidence, 
  COUNT(streetNumber) Amount, 
  COUNT(streetNumber) - 
  SUM(CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END) diff
FROM records
WHERE SUM(CASE WHEN number = streetNumber AND lname = streetName 
               THEN 1 ELSE 0 END) < COUNT(streetNumber)

This code has been made for the purpose of this question!

Upvotes: 0

Views: 70

Answers (3)

Frederic
Frederic

Reputation: 1028

if this same value is used in many places, you should create a view with a column or create a computed column in the table (I prefer the view method)

CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END

or if you just use this calculation in that place.. should use a common table expression instead the view

with my_cte as (
SELECT
  CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END AS coincidence, 
  streetNumber 
FROM records
)
SELECT
  SUM(coincidence) AS coincidence, 
  COUNT(streetNumber) Amount, 
  COUNT(streetNumber) - 
  SUM(coincidence) diff
FROM my_cte
having SUM(coincidence) < COUNT(streetNumber)

or if you don't like cte, still can you use an outer apply

SELECT
  SUM(V.Coincidence) AS coincidence, 
  COUNT(streetNumber) Amount, 
  COUNT(streetNumber) - 
  SUM(V.Coincidence) diff
FROM records
OUTER APPLY (SELECT CASE WHEN number = streetNumber AND lname = streetName 
           THEN 1 ELSE 0 END AS Coincidence) V
HAVING SUM(V.Coincidence) < COUNT(streetNumber)

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

Use a "table expression" to precompute the values, and then you can use a simpler query.

select
  coincidence,
  amount,
  amount - coincidence as diff
from ( -- here's the table expression
  select
    sum(case when number = streetnumber and lname = streetname 
             then 1 else 0 end) as coincidence, 
    count(streetnumber) as amount, 
  from records
) x
where coincidence < amount

Alternatively, some people find CTEs (Common Table Expression) more readable:

with
x as ( -- here's the CTE
  select
    sum(case when number = streetnumber and lname = streetname 
             then 1 else 0 end) as coincidence, 
    count(streetnumber) as amount, 
  from records
)
select
  coincidence,
  amount,
  amount - coincidence as diff
from x
where coincidence < amount

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I think a subquery is the simplest method:

SELECT r.*, Amount - concidence as diff
FROM (SELECT SUM(CASE WHEN number = streetNumber AND lname = streetName 
                      THEN 1 ELSE 0
                 END) AS coincidence, 
                COUNT(streetNumber) as Amount, 
      FROM records r
     ) r
WHERE coincidence < Amount;

Upvotes: 3

Related Questions