Addition of two mysql query results

Databases
1

I’m having problem with adding these two mysql query results. Can I get some help, please? What am I doing wrong?

$sql = "SELECT SUM('tcount') FROM ( SELECT 
    d1.id AS id1,
    d2.id AS id2,
    IF((d1.as1-d2.as1)=1,1,0) +
    IF((d1.as1-d2.as2)=1,1,0) +
    IF((d1.as1-d2.as3)=1,1,0) +
    IF((d1.as1-d2.as4)=1,1,0) +
    IF((d1.as2-d2.as1)=1,1,0) +
    IF((d1.as2-d2.as2)=1,1,0) +
    IF((d1.as2-d2.as3)=1,1,0) +
    IF((d1.as2-d2.as4)=1,1,0) +
    IF((d1.as3-d2.as1)=1,1,0) +
    IF((d1.as3-d2.as2)=1,1,0) +
    IF((d1.as3-d2.as3)=1,1,0) +
    IF((d1.as3-d2.as4)=1,1,0) +
    IF((d1.as4-d2.as1)=1,1,0) +
    IF((d1.as4-d2.as2)=1,1,0) +
    IF((d1.as4-d2.as3)=1,1,0) +
    IF((d1.as4-d2.as4)=1,1,0)     AS tcount
FROM astb AS d1 , astb AS d2  
WHERE d1.id IS NOT NULL AND d2.id IS NOT NULL AND d1.id > d2.id;

UNION ALL

SELECT 
    d1.id AS id1,
    d2.id AS id2,
    IF((d1.as1-d2.as1)=-1,1,0) +
    IF((d1.as1-d2.as2)=-1,1,0) +
    IF((d1.as1-d2.as3)=-1,1,0) +
    IF((d1.as1-d2.as4)=-1,1,0) +
    IF((d1.as2-d2.as1)=-1,1,0) +
    IF((d1.as2-d2.as2)=-1,1,0) +
    IF((d1.as2-d2.as3)=-1,1,0) +
    IF((d1.as2-d2.as4)=-1,1,0) +
    IF((d1.as3-d2.as1)=-1,1,0) +
    IF((d1.as3-d2.as2)=-1,1,0) +
    IF((d1.as3-d2.as3)=-1,1,0) +
    IF((d1.as3-d2.as4)=-1,1,0) +
    IF((d1.as4-d2.as1)=-1,1,0) +
    IF((d1.as4-d2.as2)=-1,1,0) +
    IF((d1.as4-d2.as3)=-1,1,0) +
    IF((d1.as4-d2.as4)=-1,1,0)     AS tcount
FROM astb AS d1 , astb AS d2  
WHERE d1.id IS NOT NULL AND d2.id IS NOT NULL AND d1.id > d2.id;) where d1.id = d2.id+1";

This is my sql fiddle. http://sqlfiddle.com/#!9/22179d/2
Thank you in advance!

2

So… many… questions.
Why are you unioning a query with the exact same query? Multiply the score by 2 instead. (EDIT: Oh i missed the negatives. Well, eliminate the union anyway, and use the ABS value of the difference.)
Why apply the restrictive where condition to the outer query but not the inner ones?
Why are we doing 16 if statements?
(Related) What are the valid values of as1…as4? Why are you subtracting each from each? (I feel like there should be a better way to get what you want. Maybe not, but just having 16 if’s makes me think ‘refine logic’)

3

thanks for your interest and questions. Here I want to compare each row with the previous row one by one. and if any of the values match the criteria given in the if statements, then it is counted in the queries. I’d like to add the results of these counts. I am sorry but I’m not familiar with restrictive condition which you mention. I am open to better suggestions if there is any for this task.

4

How about telling us about the real problem you are trying to solve instead of your attempt at solving it.

5

Here I’m trying to make a comparative table of temperature differences between dates.

6

Perfect. Tell us about that in detail. A small sample of the data would be helpful as well.

7

I’m sorry but what else you want me to tell more, i don’t understand. I think I explained and the query shows it too.

8

You may have missed my edit. A small sample of data will help to see what you are working with.

9

There is a little data on this fiddle. http://sqlfiddle.com/#!9/22179d/2

10

You will need to tell us what your cryptic column names mean. (as1, as2…) FYI: It is a much better practice to use descriptive names in your columns and variables. When you start doing consecutive column numbering it is a red flag to a bad DB design. Do all the number represent temperatures (other than id)?

I dont see a single date in your data. How are you going to compare by date?

11

yes, i use these names as column names. but if it is a problem, i can change them. Instead of dates, I used Ids for simplicity. so they are all integers.

12

Id is fine. You still haven’t said what the other columns are. We cant help you if we don’t understand what is going on.

13

there are 4 columns (as1, as2, as3, as4) and id. all integers.

14

This is going to be a long thread if you cant make it clear what is going on. I already asked you several times what that data is and how you expect to compare by dates when there are no dates.

15

I don’t know. may be the data is not visible on the link.
here my sample data:

CREATE TABLE astb (
	id INT AUTO_INCREMENT,
  	as1 INT,
  	as2 INT,
  	as3 INT,
  	as4 INT,
	PRIMARY KEY (id)
);


INSERT INTO astb VALUES
    (110, 19, 18, 15, 16),
    (109, 20, 17, 15, 15),
    (108, 24, 25, 16, 17),
    (107, 19, 18, 14, 12),   
    (106, 19, 17, 14, 13);
16

You keep refusing to answer my questions. I’m out.

17

I am sorry, I tried to explain it and provided a sqlfiddle link. thank you .

18

let me try…

what is the nature of the data in as1, as2, as3, as4? wind speeds? playoff appearances? number of girlfriends?

in your queries, you are comparing as1 to each of as1 thru as4, then as2 to each of as1 thru as4, and so on – seems like a really really bad data design, sorry to say

please explain the formula behind what you’re trying to calculate here

the weird part is your UNION… in the first half, you have a theta join on d1.id > d2.id, and in the second, just an inner join on d1.id = d2.id+1, but you’re unioning them together

in effect, you’re getting the inner join rows twice

the reason your SUM('tcount') doesn’t work is because you cannot sum a string

19

whoa, wait, i just looked at your query more closely

it does actually look like you have two identical theta joins unioned together, and then in the outer query, you’re filtering away all theta join rows except the inner join rows

most inefficient

20

these are temperatures. There are two queries here. First query to calculate the increase of +1 between different times of days. and second query for decrease of -1. Which I want to sum their counts and get a table something like this in the end. The queries work find one by one. I couldn’t sum them together only.

ID  109 108  107  106  105   

110  2   0   1   2   3   etc             
109      1   2   2   2   etc             
108          0   0   2   etc

this is just a mock up data. the reason for d1.id > d2.id is to eliminate the comparisons of the same ids. and this d1.id = d2.id+1 is probably wrong. in the previous posts, it was mentioned that multiple ifs is not the right way for this. is there a better way of finding this out?