CODEWITHSUNDEEP
sqlsql-serverselectconditional-statementsmultiple-columns
kamperone
kamperone

Reputation: 89

SQL conditional select for value in the same table group

We have a table like this:

id  mid mult    tr  result
----------------------------
1   1   1,35    0   1   
2   1   5,85    0   2   
3   1   4       1   X   
50  2   1,3     1   1   
51  2   7       0   2   
52  2   4       0   X   
99  3   2,45    0   1   
100 3   2,2     0   2   
101 3   3,3     1   X   
105 4   2,3     0   1   
106 4   2,4     0   2   
107 4   3,2     1   X   
111 5   3       1   1   
112 5   1,9     0   2   
113 5   3,25    0   X

What we need is a query that will bring us a table which will count how many times before, for a specific mid(match), have the same mults(multipliers) for result = 1 and result = x have occurred and group them so that we can count them. Something like

select mult(where result = 1), mult(where result = x), count(1)

The result will be like this

mult_1      mult_x      count
------------------------------
1,35        4           33
1,3         4           112

The above states that we have seen 33 matches where the mult for result = 1 was 1,35 AND mult for result = x was 4. Also, we found 112 matches where multi for result = 1 was 1,3 AND for result = x mult was 4 (results are not dependant on the first table).

I find it rather complex myself, but hopefully I made sense. I use SQL Server 2008, so any method is more than welcome.

Upvotes: 0

Views: 548

Answers (2)

Jeremy Pridemore
Jeremy Pridemore

Reputation: 1995

Here is my (final) solution in action: http://www.sqlfiddle.com/#!3/3a516/7

There are a number of assumptions I'm making here.

  • I'm assuming that every mid value is going to have at most 1 result value of '1' and 1 result of '2'.
  • I'm assuming that if you want to specify a mid and have the query run off of that.
    • This assumption turned out to be wrong.
  • I'm assuming that you want to include the data for the mid you specify.
    • This assumption turned out to be wrong, it was every match.
  • I'm assuming that you want to include every mid (or match) before the mid you specify.
    • This assumption turned out to be wrong, it was every match.
  • I'm assuming that for any mult_1 and mult_x combination you find in this way, you want the count of how many times that combination occurs, even if it occurs after the match(mid) that you specified.
    • This assumption turned out to be wrong, it was just for every match.

If all of these assumptions sound right, then this might create the result you're looking for:

DECLARE @Mid INT = 2

;WITH MatchResults AS
(
  SELECT
    Mid
    , [1] AS MultWithResult1
    , [X] AS MultWithResultX
  FROM
  (
    SELECT
      Mid
      , mult
      , result
    FROM Matches
    WHERE result IN ('1', 'X')
  ) Base
  PIVOT
  (
    MAX(mult)
    FOR result
    IN
    (
      [1]
      , [X]
    )
  ) Pivoted
)
SELECT
  mult.MultWithResult1 AS mult_1
  , mult.MultWithResultX AS mult_x
  , COUNT(*) AS [count]
FROM MatchResults mult
GROUP BY mult.MultWithResult1
  , mult.MultWithResultX

EDIT: I have edited it based on the response to my answer to what I think he means.

Upvotes: 2

JotaBe
JotaBe

Reputation: 39004

Use this query:

select mult_1, mult_x, count() as count
from Matches M
inner join (select distinct M1.mult as mult_1, MX.mult as mult_x from 
           matches as M1, matches as MX
           where M1.result=1 and MX.result=x) M1x
  on (M.mult=M1x.mult_1 and M.result=1) or (M.mult=M1x.mult_x and result=x)
group by mult_1, mult_x

EDIT: I'm supposing you wnat to get the count of all possible combinations of multipliers of matches with result=1 and matches with result = x.

If this is the case, M1x gives all this possible combinations. And you join all the possible matchea with have any of those combinations, and count them, grouping by the possible set of combinations defined by M1x.

Upvotes: 1

Related Questions