CODEWITHSUNDEEP
phpmysqlsql
Chris Pasterel
Chris Pasterel

Reputation: 3

IDs from count query

I have table Orders:

O_Id    OrderDate   OrderPrice  Customer
1       2008/11/12  1000        Hansen
2       2008/10/23  1600        Nilsen
3       2008/09/02  700         Hansen
4       2008/09/03  300         Hansen
5       2008/08/30  2000        Jensen
6       2008/10/04  100         Nilsen

and query:

SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen'

but is possible add to this results IDs results? I would like receive count: 2 and ids: 2 and 6

Upvotes: 0

Views: 65

Answers (5)

bvr
bvr

Reputation: 4826

Try this

SELECT O1.O_Id AS OrderID,Count(O1.O_Id) AS OrderCount FROM Orders  O1
INNER JOIN Orders  O2 ON O1.Customer = O2.Customer 
WHERE O1.Customer='Nilsen'
GROUP BY O1.Customer,O1.O_Id

Upvotes: 0

Narek
Narek

Reputation: 3823

GROUP_CONCAT may help:

SELECT
    COUNT(Customer) AS CustomerNilsen,
    GROUP_CONCAT(O_Id) as IDS
FROM
    Orders
WHERE
    Customer='Nilsen'

Upvotes: 4

silkfire
silkfire

Reputation: 25945

Better to just fetch all the IDs and then use the appropriate row count function:

SELECT `O_Id` FROM `Orders`
WHERE `Customer` = 'Nilsen'

Upvotes: 1

user1088172
user1088172

Reputation:

You might check this 

SELECT Customer,
         COUNT(Customer) AS CustomerNilsen  
    FROM Orders 
   WHERE Customer = 'Nilsen'
GROUP BY Customer
  HAVING CustomerNilsen  = 1

Upvotes: 0

George
George

Reputation: 36784

I don't really understand your question, but from what I can tell you just want to SELECT the o_id column in your query:

SELECT COUNT(Customer) AS CustomerNilsen, O_Id AS OrderID FROM Orders WHERE Customer='Nilsen'

Upvotes: 0

Related Questions