[RESOLVED] query problem

[Riche]

Hi Iam trying to pull data from a couple of tables.. which I have managed to do on a per customer basis with this query

SELECT company_name AS 'Company Name', handheld_type AS 'Handheld Type', COUNT(*) AS `Number`
from hhcs join company_details 
on hhcs.companyid = company_details.aid where companyid='57' GROUP BY `handheld_type`
ORDER BY `handheld_type` ASC

I now need to get the whole database not just for companyid 57.. but when I remove that part of the query it goes pear shaped.

The tables are set out like this:

CREATE TABLE company_details (
aid int(12) NOT NULL auto_increment,
owner varchar(255) NOT NULL,
company_name varchar(255) default NULL,
council_code varchar(255) NOT NULL default '',
notes text,
software varchar(255) NOT NULL,
handheld_sw_version varchar(255) default NULL,
comms_sw_version varchar(255) default NULL,
hostname varchar(255) NOT NULL default '',
subnet varchar(255) NOT NULL default '',
logo varchar(255) NOT NULL,
PRIMARY KEY (aid)
) ENGINE=InnoDB

CREATE TABLE hhcs (
hid int(12) NOT NULL auto_increment,
companyid int(12) default NULL,
handheld_type varchar(255) default NULL,
serial_number varchar(255) default NULL,
machine_number int(4) default NULL,
dispatch_date datetime default NULL,
notes varchar(255) default NULL,
mac_address varchar(255) default NULL,
ipaddress decimal(12,0) NOT NULL default '0',
PRIMARY KEY (hid)
) ENGINE=InnoDB;

The companyid and aid are linked.

any ideas?

Thanks for any help

Rich

[leatherback]

'it goes pear shaped.'

What do you mean? What happens that should not happen?

[Riche]

The counts are wrong for the different handheld_types and only 56 rows are returned when there should be alot more.

There are around a 100 different company_names each having 2-8 different handheld_types.

So what I want is:

Company_name1
Handheld_type1 10
Handheld_type2 9
Handheld_type3 6
Handheld_type4 1

Company_name2
Handheld_type1 5
Handheld_type2 6
Handheld_type3 3
Handheld_type4 7

etc

[leatherback]

the GROUP BY handheld_type tells the server to only return 1 row for each halndheld type. Remove that. That might already solve it.

I am not sure what the simple join command results in. I usually use a left join in my queries.

[Riche]

This happens:
SQL query:

SELECT company_name AS 'Company Name', handheld_type AS 'Handheld Type', COUNT( * ) AS Number
FROM hhcs
JOIN company_details ON hhcs.companyid = company_details.aid
ORDER BY handheld_type ASC

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Thanks for trying

[leatherback]

yes. This is the result of the count(*). If you are not grouping your results (You now want to show all handhelds for all companies) there is nothing to count, and as such it doesn't like the count() statement.

in your little list I see however a number after each item. What do they mean?

[Lars_Berg]

select company_name as `Company Name`,
          handheld_type AS `Handheld Type`, 
          count(*) AS `Number`
  from hhcs join company_details
     on hhcs.companyid = company_details.aid 
 group by `company_name`,`handheld_type` 
 order by `handheld_type` 

[Riche]

Thankyou that worked great.