CODEWITHSUNDEEP
phpmysqlsql
user3295583
user3295583

Reputation:

Using a Select query inside a select query?

I am trying to use a select query within another select query in the from clause.This is a mysql query.

SELECT invoiceNo, SUM(weight), COUNT(barcode), sum_total
FROM (SELECT * FROM `selected_items` WHERE date LIKE '07-Jan-2016' ORDER BY id DESC)
WHERE date LIKE '07-Jan-2016' GROUP BY invoiceNo;

This is how my database looks like. enter image description here How can I achieve something like this where I have a table which I order by id desc and use another select query on the result of this query.The above query gives me an error #1248 - Every derived table must have its own alias.Can anyone help I am new to programming.

Upvotes: 1

Views: 4263

Answers (4)

Mohit Aggarwal
Mohit Aggarwal

Reputation: 208

I didn't get it why you are using sub-query. You can directly use the table 'selected_items' and check the condition in where clause.Like:

SELECT invoiceNo, SUM(weight), COUNT(barcode)
from 'selected_items'
WHERE date LIKE '07-Jan-2016' 
GROUP BY invoiceNo
Order By Id

Upvotes: 1

user3848444
user3848444

Reputation:

I hope this will help you out .

SELECT invoiceNo, SUM( weight ) , COUNT( barcode ) , sum_total
FROM (SELECT * FROM  `selected_items` 
WHERE DATE LIKE  '07-Jan-2016'
ORDER BY id DESC
)a
GROUP BY invoiceNo;

Upvotes: 1

Randz Rover
Randz Rover

Reputation: 45

You're query is almost right. However there's only missing. You must used an ALIAS.

SELECT * FROM ( SELECT * FROM @table )AS tbl1 WHERE field1 = 'test'

Upvotes: 0

Utsav
Utsav

Reputation: 8143

You dont need to do order by inside inner select query. And in MySQL, you need to give an alias to a derived table, a in this example. 

SELECT invoiceNo, SUM(weight), COUNT(barcode), sum_total
FROM 
(
SELECT * FROM `selected_items` WHERE date LIKE '07-Jan-2016'
) a
WHERE date LIKE '07-Jan-2016' GROUP BY invoiceNo;

Without looking at data, it is difficult to decide to order by but this query will work for you.

Upvotes: 0

Related Questions