1

I'm trying to write a script that counts results based on 2 fields matching - but not matching like identically, but where the values re-occur throughout the table.

For example, I want to find where Field A and Field B = x & y, respectively (and count those results) however, field A isn't always X and field B isn't always Y. Also, Field A and Field B values are unknown. Here's what I've written so far:

select a.fielda, b.fieldb, count (*)
from tableA a
join tableB b
on a.fieldd = b.fieldd

where a.fielda = b.fieldb --I know this is a problem, just for notes on what I'm trying to accomplish.
group by b.fieldb, a.fielda
order by b.fieldb.

I'm a newb here so any help will be greatly appreciated. Thank you in advance.

DJDJ23
  • 139
  • 1
  • 2
  • 12
  • 1
    Some sample data and expected result would make this much clearer. – jarlh May 12 '15 at 13:05
  • Just remove the `where a.fielda = b.fieldb`line altogether. What's left should be the correct query based on what you've described. – Tobsey May 12 '15 at 13:08
  • Tobsey, you were right. I didn't think that by selecting those two fields, I was already finding records where those values were the same. THANK YOU!!!!! – DJDJ23 May 12 '15 at 13:26

2 Answers2

0
SELECT SUM(CASE WHEN a.fielda = b.fieldb THEN 1 ELSE NULL END) AS MatchCount
     , SUM(CASE WHEN a.fielda = x and b.fieldb = y THEN 1 ELSE NULL END) AS XYCount
     , COUNT(*) AS FieldDMatchCount
FROM tableA a
JOIN tableB b
  ON a.fieldd = b.fieldd
Rob Paller
  • 7,736
  • 29
  • 26
0

Tobsey had it correct for me. I didn't think that by selecting the two fields that I was already finding records where those two fields existed together...brain fart, I guess. Thank you for the help!

DJDJ23
  • 139
  • 1
  • 2
  • 12