user3164900 user3164900 - 7 months ago 22
SQL Question

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:


Answer

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