SUM () ,CROSS JOIN Query

Question

I've MYSQL Query with SUM & CROSS JOIN, its working fine.

MYSQL QUERY:

SELECT SUM(totexec/(pass_count+fail_count))/d.total  COUNT FROM test_schedule CROSS JOIN ( SELECT COUNT(*) total FROM test_schedule WHERE project='JupiterQA' ) d WHERE project='JupiterQA'

But I'm trying to execute this same query in MSSQL in showing "Column 'd.total' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

After that I added GROUP BY a.total in end of the query, ERROR IS: Divide by zero error encountered.

I've records in all the table,Is Query wrong?

must `a.total ` `d.total` in group by? – Jens Feb 11 '15 at 07:47 — Jens, Feb 11 '15 at 07:47
I"ve only alias d, so that am using d.total. but a.total? – user3114967 Feb 11 '15 at 07:55 — user3114967, Feb 11 '15 at 07:55
Is in your question "GROUP BY a.total". – Jens Feb 11 '15 at 07:59 — Jens, Feb 11 '15 at 07:59
Oh sorry that one is d.total only – user3114967 Feb 11 '15 at 08:13 — user3114967, Feb 11 '15 at 08:13

Alexis Peters · Answer 1 · 2015-02-11T07:55:23.887

0

i'm not askin why you use a cross join :D just add a group by clause in your query

SELECT SUM(totexec(pass_count+fail_count))d.total  COUNT FROM test_schedule CROSS JOIN ( SELECT COUNT(*) total FROM test_schedule WHERE project='JupiterQA' ) d WHERE project='JupiterQA' GROUP BY d.total

edited Feb 11 '15 at 07:55

answered Feb 11 '15 at 07:48

Alexis Peters

1,583
1
10
17

I added GROUP BY d.total after that only I got error.I mentioned above question itself. – user3114967 Feb 11 '15 at 07:53
okay, why are you using slashes ? mssql may interpret this to divide by d.total – Alexis Peters Feb 11 '15 at 07:55
am doing divide operation,without slashes how can do the divide operation? – user3114967 Feb 11 '15 at 07:58

Giorgos Betsos · Answer 2 · 2015-02-11T08:10:35.540

0

This should be equivalent to the MYSQL query:

SELECT tSum / dTotal AS COUNT
FROM (
   SELECT SUM(totexec / (pass_count+fail_count)) AS tSum, d.total AS dTotal 
   FROM test_schedule 
   CROSS JOIN ( SELECT COUNT(*) total 
                FROM test_schedule 
                WHERE project='JupiterQA' ) d 
   WHERE project='JupiterQA' ) t

I don't see a need for a CROSS JOIN though. This should also do the job:

SELECT tSum / ( SELECT COUNT(*) 
                FROM test_schedule 
                WHERE project='JupiterQA' ) AS COUNT
FROM (
   SELECT SUM(totexec / (pass_count+fail_count)) AS tSum 
   FROM test_schedule 
   WHERE project='JupiterQA' ) t

edited Feb 11 '15 at 08:10

answered Feb 11 '15 at 08:04

Giorgos Betsos

71,379
9
63
98

its throwing divide by zero error, but I have data's – user3114967 Feb 11 '15 at 08:12
@user3114967 Either `pass_count+fail_count` or `SELECT COUNT(*) total FROM test_schedule WHERE project='JupiterQA'` is zero. – Giorgos Betsos Feb 11 '15 at 08:14
@user3114967 Do a select on `pass_count+fail_count` and `SELECT COUNT(*) total ...` first to see what values you get. – Giorgos Betsos Feb 11 '15 at 08:19
sum (pass_count+fail_count) value is: 1432 SELECT COUNT(*) total FROM test_schedule WHERE project='JupiterQA' value is :1257 – user3114967 Feb 11 '15 at 09:44
@user3114967 What do you get if you execute: `SELECT SUM(totexec / (pass_count+fail_count)) FROM test_schedule WHERE project = 'JupiterQA'`? – Giorgos Betsos Feb 11 '15 at 10:32
@user3114967 Did you finally managed to tackle division by zero error? I am just curious as there is no obvious reason for this error to occur. – Giorgos Betsos Feb 15 '15 at 18:15

SUM () ,CROSS JOIN Query

2 Answers2