Link to home
Create AccountLog in
Avatar of Brian Selltiz
Brian SelltizFlag for United States of America

asked on

SQL Grouping Question

Can you tell me why this SQL query is producing unwanted results? I don't just want to fix it, I want to learn how to not make this mistake again.

 

For example, site 447 and Device Axis P3719-PLE (exterior) should really be quantity 3.

 

SELECT

   assembly.friendlyName,

   COUNT(assembly.friendlyName) AS Total,

  location.ID,

  location.name

FROM device

  INNER JOIN location

    ON device.locationID = location.ID

  INNER JOIN floor_plan

    ON floor_plan.locationID = location.ID

  INNER JOIN assembly

    ON device.assemblyID = assembly.ID

WHERE device.wireID = 12

AND device.quoteID = 255

AND location.siteID = 142

GROUP BY assembly.friendlyName,

         location.ID,

         location.name


User generated image


Avatar of David H.H.Lee
David H.H.Lee
Flag of Malaysia image

Can you tell me why this SQL query is producing unwanted results? I don't just want to fix it, I want to learn how to not make this mistake again.

You should only group by the columns that uniquely identify each assembly for the purpose of your count.  


Example:

SELECT
   assembly.friendlyName,
   COUNT(*) AS Total
FROM device
  INNER JOIN location
    ON device.locationID = location.ID
  INNER JOIN floor_plan
    ON floor_plan.locationID = location.ID
  INNER JOIN assembly
    ON device.assemblyID = assembly.ID
WHERE device.wireID = 12
  AND device.quoteID = 255
  AND location.siteID = 142
GROUP BY assembly.friendlyName;

Open in new window


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
The location isn't in the results image so the SQL you posted doesn't seem to have produced the results in the image?

>>Device Axis P3719-PLE (exterior) should really be quantity 3

Unless we see the raw data I'm not sure how we can guess why the results in the image shows 9.

Look at the raw data returned and see if what is missing jumps out at you(untested):
SELECT
   *
FROM device
  INNER JOIN location
    ON device.locationID = location.ID
  INNER JOIN floor_plan
    ON floor_plan.locationID = location.ID
  INNER JOIN assembly
    ON device.assemblyID = assembly.ID
WHERE device.wireID = 12
AND device.quoteID = 255
AND location.siteID = 142
and location.id=447
and assembly.friendlyName='Axis P3719-PLE (exterior)'

Open in new window


If you are getting more rows than you expect then I'm guessing you are missing a join column?
Avatar of Brian Selltiz
Brian Selltiz
Flag of United States of America image

ASKER

Hi, I left out the location field due to privacy concerns but the Location ID is one to one with the location name so there would be no difference seeing that data. So the raw data shows this for ID 447, then below are the full results


User generated image

locationID   friendlyName   internalID   wireID   quoteID

444   AXIS M4216-LV Dome Camera   CAM-6   12   255

444   Axis P3267-LV Dome Camera   CAM-7   12   255

444   AXIS M4216-LV Dome Camera   CAM-8   12   255

444   AXIS M4216-LV Dome Camera   CAM-9   12   255

444   Axis P3267-LV Dome Camera   CAM-10   12   255

444   AXIS M4216-LV Dome Camera   CAM-12   12   255

444   Axis P4705-PLVE   CAM-13   12   255

444   AXIS M4216-LV Dome Camera   CAM-14   12   255

444   Axis P4705-PLVE   CAM-17   12   255

444   AXIS M4216-LV Dome Camera   CAM-22   12   255

444   Axis P3267-LV Dome Camera   CAM-24   12   255

444   Axis P3267-LV Dome Camera   CAM-25   12   255

444   AXIS M4216-LV Dome Camera   CAM-26   12   255

444   AXIS M4216-LV Dome Camera   CAM-27   12   255

444   AXIS M4216-LV Dome Camera   CAM-28   12   255

444   Axis P3268-LV Dome Camera   CAM-29   12   255

444   Axis P3268-LV Dome Camera   CAM-30   12   255

444   AXIS M4216-LV Dome Camera   CAM-34   12   255

444   Axis P3267-LV Dome Camera   CAM-35   12   255

444   Axis P3267-LV Dome Camera   CAM-36   12   255

444   Axis P3267-LV Dome Camera   CAM-37   12   255

444   AXIS M4216-LV Dome Camera   CAM-38   12   255

444   Axis P3268-LV Dome Camera   CAM-39   12   255

444   Axis P3268-LV Dome Camera   CAM-40   12   255

444   Axis P3268-LV Dome Camera   CAM-42   12   255

444   AXIS M4216-LV Dome Camera   CAM-43   12   255

444   AXIS M4216-LV Dome Camera   CAM-44   12   255

444   AXIS M4216-LV Dome Camera   CAM-45   12   255

444   AXIS M4216-LV Dome Camera   CAM-46   12   255

444   Axis P3268-LV Dome Camera   CAM-47   12   255

444   Axis P3268-LV Dome Camera   CAM-50   12   255

444   Axis P3267-LV Dome Camera   CAM-55   12   255

444   AXIS M4216-LV Dome Camera   CAM-56   12   255

444   Axis P4705-PLVE   CAM-59   12   255

444   Axis P3267-LV Dome Camera   CAM-60   12   255

444   AXIS M4216-LV Dome Camera   CAM-61   12   255

444   AXIS M4216-LV Dome Camera   CAM-62   12   255

444   AXIS M4216-LV Dome Camera   CAM-63   12   255

444   AXIS M4216-LV Dome Camera   CAM-64   12   255

444   Axis P3267-LV Dome Camera   CAM-65   12   255

444   AXIS M4216-LV Dome Camera   CAM-66   12   255

444   Axis P4705-PLVE   CAM-67   12   255

444   Axis P4705-PLVE   CAM-80   12   255

444   Axis P4705-PLVE   CAM-81   12   255

444   AXIS M4216-LV Dome Camera   CAM-82   12   255

444   AXIS M4216-LV Dome Camera   CAM-83   12   255

444   AXIS M4216-LV Dome Camera   CAM-84   12   255

444   AXIS M4216-LV Dome Camera   CAM-85   12   255

444   AXIS M4216-LV Dome Camera   CAM-87   12   255

444   Axis P4705-PLVE   CAM-93   12   255

444   Axis P4705-PLVE   CAM-96   12   255

444   AXIS M4216-LV Dome Camera   CAM-99   12   255

444   Axis P3719-PLE (exterior)   CAM-125   12   255

444   Axis P3719-PLE (exterior)   CAM-127   12   255

444   Axis P3719-PLE (exterior)   CAM-128   12   255

444   Axis P3719-PLE (exterior)   CAM-130   12   255

444   Axis P3719-PLE (interior)   CAM-131   12   255

444   Axis P3719-PLE (interior)   CAM-132   12   255

444   Axis P3719-PLE (interior)   CAM-133   12   255

444   Axis P3719-PLE (interior)   CAM-134   12   255

444   Axis P3719-PLE (interior)   CAM-135   12   255

444   Axis P3719-PLE (exterior)   CAM-136   12   255

444   Axis P3719-PLE (exterior)   CAM-137   12   255

444   Axis P3719-PLE (exterior)   CAM-138   12   255

444   Axis P3719-PLE (exterior)   CAM-139   12   255

444   Axis P3719-PLE (exterior)   CAM-141   12   255

444   Axis P3267-LV Dome Camera   CAM-142   12   255

444   Axis P3267-LV Dome Camera   CAM-144   12   255

444   AXIS M4216-LV Dome Camera   CAM-145   12   255

445   AXIS M4216-LV Dome Camera   CAM-3   12   255

445   Axis P3267-LV Dome Camera   CAM-4   12   255

445   AXIS M4216-LV Dome Camera   CAM-24   12   255

445   Axis P3719-PLE (interior)   CAM-25   12   255

445   AXIS M4216-LV Dome Camera   CAM-27   12   255

445   Axis P3719-PLE (interior)   CAM-37   12   255

445   Axis P3268-LV Dome Camera   CAM-42   12   255

445   Axis P3268-LV Dome Camera   CAM-43   12   255

445   Axis P3268-LV Dome Camera   CAM-50   12   255

445   Axis P3268-LV Dome Camera   CAM-51   12   255

445   Axis P3268-LV Dome Camera   CAM-57   12   255

445   AXIS M4216-LV Dome Camera   CAM-62   12   255

445   Axis P3267-LV Dome Camera   CAM-63   12   255

445   Axis P3267-LV Dome Camera   CAM-65   12   255

445   Axis P3267-LVE Dome Camera   CAM-68   12   255

445   AXIS M4216-LV Dome Camera   CAM-77   12   255

445   AXIS M4216-LV Dome Camera   CAM-81   12   255

445   AXIS M4216-LV Dome Camera   CAM-84   12   255

445   AXIS M4216-LV Dome Camera   CAM-88   12   255

445   Axis P4705-PLVE   CAM-91   12   255

445   Axis P3719-PLE (interior)   CAM-109   12   255

445   Axis P3719-PLE (interior)   CAM-110   12   255

445   Axis P3719-PLE (interior)   CAM-112   12   255

445   Axis P3719-PLE (interior)   CAM-113   12   255

445   Axis P3719-PLE (interior)   CAM-116   12   255

445   Axis P3719-PLE (interior)   CAM-117   12   255

445   Axis P3267-LV Dome Camera   CAM-126   12   255

445   Axis P3268-LV Dome Camera   CAM-128   12   255

445   AXIS M4216-LV Dome Camera   CAM-129   12   255

445   Axis P3719-PLE (interior)   CAM-130   12   255

445   Axis P3719-PLE (interior)   CAM-132   12   255

445   AXIS M4216-LV Dome Camera   CAM-134   12   255

445   Axis P3719-PLE (interior)   CAM-136   12   255

445   Axis P3719-PLE (exterior)   CAM-137   12   255

445   Axis P3719-PLE (exterior)   CAM-138   12   255

445   Axis P3719-PLE (exterior)   CAM-139   12   255

446   Axis P3719-PLE (interior)   CAM-40   12   255

447   AXIS M4216-LV Dome Camera   CAM-26   12   255

447   AXIS M4216-LV Dome Camera   CAM-34   12   255

447   Axis P3719-PLE (exterior)   CAM-47   12   255

447   Axis P3719-PLE (interior)   CAM-50   12   255

447   Axis P3719-PLE (exterior)   CAM-51   12   255

447   Axis P3719-PLE (exterior)   CAM-52   12   255

448   Axis P3719-PLE (exterior)   CAM-19   12   255

448   Axis P3719-PLE (interior)   CAM-59   12   255

448   Axis P3268-LVE   CAM-62   12   255

448   Axis P3719-PLE (exterior)   CAM-63   12   255

449   Axis P3719-PLE (exterior)   CAM-49   12   255

449   Axis P3719-PLE (exterior)   CAM-50   12   255

450   Axis P3267-LVE Dome Camera   CAM-8   12   255

450   Axis P3719-PLE (interior)   CAM-10   12   255

450   Axis P3719-PLE (exterior)   CAM-34   12   255

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
>>but the Location ID is one to one with the location name so there would be no difference seeing that data

Location name?

Remember that we don't know anything about your tables and data so what is obvious to you may not be to us.

Without lower level, specific details, it is almost impossible to point to a SQL statement and point to a specific line causing the problem.

>>So the raw data shows this for ID 447, then below are the full results

I don't know where the data you posted comes from.  One of the 4 tables in the query, some query joining the tables, ???

I'm still thinking there is either a missing join column or possibly a data issue you aren't expecting.

For example:  If there is 3 rows for 'Axis P3719-PLE (exterior)' are in the device table and 3 rows for the assemblyID in assembly, you'll get your 9 rows.  Maybe 3 locations?

I would start with a single query from the driving table, then query the other tables individually using the values from the prior query.

So if 'Axis P3719-PLE (exterior)' is assembly.id of 447, then:
select * from device where assemblyID=447;

The look at the returned rows and pull out the locationID's from the device table to look at the locations, etc...
Avatar of Brian Selltiz
Brian Selltiz
Flag of United States of America image

ASKER

I re-wrote it and now I have it working. Thanks for your help, I appreciate it.

ASKER CERTIFIED SOLUTION
Avatar of Brian Selltiz
Brian Selltiz
Flag of United States of America image
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer