0

im geting this error when trying to do 2 counts inside of my query

first ill show you the query:

$sql = mysql_query("select c.id, c.number, d.name, 
                    (select count(*) from `parts` where `id_container`=c.id group by `id_car`) as packcount,
                    (select count(*) from `parts` where `id_container`=c.id) as partcount
                    from `containers` as c
                    left join `destinations` as d on (d.id = c.id_destination)
                    order by c.number asc") or die(mysql_error());

now the parts table has 2 fields that i need to use in the count: id_car id_container

id_car = the ID of the car the part is for id_container = the ID of the container the part is in

for packcount all i want is a count of the total cars per container for partcount all i want it a count of the total parts per container

thenetimp
  • 9,487
  • 5
  • 29
  • 42
scarhand
  • 4,269
  • 23
  • 63
  • 92
  • You have a group by in your packcount line and you're trying to return that as a single column value. – StudyOfCrying Feb 09 '12 at 22:38
  • If you really want help with this, I suggest editing your question to include examples of table definitions and data as well as the desired output for your query. Thank you. – StudyOfCrying Feb 09 '12 at 22:42
  • as StudyOfCrying said, can you please put table structure, inserts and what you want out of your query? It will go much faster that way – Bhrugesh Patel Feb 09 '12 at 23:14

3 Answers3

2

It's because of GROUP BY You're using

Try something like

(select count(distinct id_car) from `parts` where `id_container`=c.id)

in You're subquery (can't check right now)

EDIT

PFY - I think UNIQUE is for indexes

Xander
  • 1,114
  • 2
  • 9
  • 18
  • Yea, I was in the middle of indexing a table when I wrote that and confused myself :) – PFY Feb 10 '12 at 03:57
0

Your grouping in your first sub-query is causing multiple rows to be returned, you will probably need to run separate queries to get the results you are looking for.

PFY
  • 326
  • 1
  • 7
  • Have you tried select UNIQUE count(id_car) in your first sub query? -edit- Xander beat me to it – PFY Feb 09 '12 at 22:47
0

This subquery may return more than one row.

(select count(*) from `parts` where `id_container`=c.id group by `id_car`) as packcount, ...

so, i'd suggest to try something of the following:

(select count(DISTINCT `id_car`) from `parts` where `id_container`=c.id) as packcount, ...

see: COUNT(DISTINCT) on dev.mysql.com

and: QA on stackoverflow

Community
  • 1
  • 1
mjStallinger
  • 192
  • 2
  • 15