CODEWITHSUNDEEP
sqlsqlite
Muhammad Hashim Shafiq
Muhammad Hashim Shafiq

Reputation: 653

Ambiguous column name Error in SQL Query

I have two Queries. 1st one is:

SELECT hardware, SUM(Quantity) FROM request_1v2 where date between "2016-03-12" AND "2016-03-14" group by hardware

Output of 1st Query is

2nd Query is:

SELECT hardware, SUM(Quantity) FROM request_1v2 where date between "2016-03-12" AND "2016-03-12" group by hardware

Output of 2nd Query is:

Actually I am plotting a graph So I need to merge these two queries into a single one. The solution I got is :

SELECT t1.hardware, SUM(t1.Quantity), SUM(t2.Quantity) 
FROM request_1v2 AS t1,request_1v2 AS t2 
WHERE t1.date between "2016-03-12" and "2016-03-14" AND t2.date between "2016-03-12" and "2016-03-12" 
GROUP BY t1.hardware

Output of Solution Query:

This is wrong !

Actual Output that I need will be:-

I don't know how to do it. Can anyone help me.? Someone suggested me to write your query as:-

SELECT hardware, SUM(t1.Quantity), SUM(t2.Quantity) 
FROM request_1v2 AS t1,request_1v2 AS t2 
WHERE t1.date between "2016-03-12" and "2016-03-14" AND t2.date between "2016-03-12" and "2016-03-12" 
GROUP BY hardware

But it gives error "ambiguous column name: hardware"

Please help me and write the Query as your answer Thanks.

If you need Table Structure Here it is:

Upvotes: 1

Views: 650

Answers (1)

Mihai
Mihai

Reputation: 26784

Just use a CASE,thing is your dates overlap,you might want to change them.Your third query creates a cartesian JOIN since you dont have a JOIN condition.

SELECT hardware, 
SUM(CASE WHEN date between "2016-03-12" AND "2016-03-14" THEN Quantity ELSE 0 END),
SUM(CASE WHEN date between "2016-03-12" AND "2016-03-12" THEN Quantity ELSE 0 END)
 FROM request_1v2 
group by hardware

Upvotes: 1

Related Questions