Subscribe to this thread
Home - General / All posts - Count query M8
ColinD


1,886 post(s)
#05-Aug-18 02:22

I have a Table with columns [Attribute] and [Polygon] with several of the same Attributes per Polygon type. I repeat the following query to get the count of each Attribute for each Polygon type. Then I manually combine the results into a single table. What is the syntax to create a single output table?

Select count (Attribute) as AttributeCount, (Polygon) as PatchType from [Table] 

where [Table].[Attribute] like "A1" group by [Polygon];

The final table has columns [AttributeCount] [PatchType] and [Attribute].

Thanks


Aussie Nature Shots

tjhb

8,410 post(s)
#05-Aug-18 04:48

Colin,

Do you mean that you currently use several similar queries to count different attributes by polygon type?

And you want to do all the counting in one query (giving one output table)?

In that case I think you will want to group by both polygon type and attribute.

Should any existing attribute values be excluded from counting? If so, is there a list of all attributes to be counted?

Last question. If for a given polygon type, a given attribute is repeated (appears more than once, perhaps several or many times), should the repeats be included in the count, or do you only want to count distinct attribute values?

ColinD


1,886 post(s)
#05-Aug-18 05:17

Do you mean that you currently use several similar queries to count different attributes by polygon type? Yes

And you want to do all the counting in one query (giving one output table)? Yes

In that case I think you will want to group by both polygon type and attribute - so I thought but couldn't get the correct syntax.

Should any existing attribute values be excluded from counting? No

If for a given polygon type, a given attribute is repeated (appears more than once, perhaps several or many times), should the repeats be included in the count, or do you only want to count distinct attribute values? Repeats should be included.

Thanks Tim


Aussie Nature Shots

tjhb

8,410 post(s)
#05-Aug-18 06:23

Great.

I should have asked about the LIKE operator as well. Do you mean LIKE "A1%", meaning that all attrbutes starting with "A1" should be counted together? If so, what do the specifers for the other attribute groups look like? E.g. always just the first two characters? Or are there more detailed rules?

Or have I got the wrong end of the stick? Is LIKE a red herring?

ColinD


1,886 post(s)
#05-Aug-18 06:40

Sorry, Like should be =


Aussie Nature Shots

tjhb

8,410 post(s)
#05-Aug-18 07:44

That's simpler then! This should be enough. (And sorry, my typing will smell a bit since I've been gutting garfish. Yum.)

--SQL

SELECT 

    [Polygon] as [PatchType][Attribute],

    COUNT(*) AS [AttributeCount]

FROM [Table]

GROUP BY [Polygon][Attribute]

;

ColinD


1,886 post(s)
#05-Aug-18 08:32

Yes, that did it, thanks. Enjoy the garfish!


Aussie Nature Shots

tjhb

8,410 post(s)
#05-Aug-18 09:04

Thanks, bliss. Sorry for overcomplicating things.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.