CODEWITHSUNDEEP
mysqlsql
kratika gupta
kratika gupta

Reputation: 91

table aliasing not working in mysql

my table consists of a column OPEN_POS1 and another column Lead_time_Bucket. I want to find the sum of all OPEN_POS1 with Lead_time_Bucket '0 to 15', '16 to 30' and '> 30' in three different columns. But the output is not correct for the following query.

select sum(x.OPEN_POS1) as '0-15',sum(y.OPEN_POS1) as '16-30',sum(z.OPEN_POS1) as '>30'
from `table 2` as x,`table 2` as y,`table 2` as z 
where x.Lead_time_Bucket='0 to 15'
and y.Lead_time_Bucket='16 to 30'
and z.Lead_time_Bucket='> 30'

Upvotes: 2

Views: 67

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Just use conditional aggregation. You don't need three joins:

select sum(case when Lead_time_Bucket = '0 to 15' then OPEN_POS1 else 0 end) as `0-15`,
       sum(case when Lead_time_Bucket = '16 to 30' then OPEN_POS1 else 0 end) as `16-30`,
       sum(case when Lead_time_Bucket = '> 30' then OPEN_POS1 else 0 end) as `>30`
from `table 2`;

Also:

Use single quotes only for date and string constants. This will prevent future problems. And, if you are going to use joins, learn explicit join syntax.

Upvotes: 2

Mureinik
Mureinik

Reputation: 312219

You do not have a join clause, so you're effectively querying a Cartesian join of each row of x times each row of y times each row of z.

For this usecase, however, you don't need self-joins - you could just use sum with a group by clause:

SELECT   lead_time_bucket, SUM (open_pos1)
FROM     `table 2`
WHERE    lead_time_bucket IN ('0 to 15', '16 to 30', '> 30'
GROUP BY lead_time_bucket

Upvotes: 0

Related Questions