Total Newb needs help with Query

English
#1

I have very little experience with database in general, but I’m willing to learn. I have created a small database that currently has less than 3500 records. I’m using the LibreBase with HSQLDB Embedded.
I have only one table and this table has 7 total columns, one of which is my Primary Key and one of which is relatively independent of the other 5. The 5 remaining columns are all integers and each record is distinct in the fact that no integer is repeated in any record. The integers can range from 1 to 75, with no negatives and the records do not contain any Null values.
I would like to be able to query the database and total the number of occurrences of each different integer. Grouping them in sequence and ordering them by the primary key.
I tried something like

SELECT FIRST_COL, SEC_COL, THIRD_COL, FOURTH_COL, FIFTH_COL, COUNT(*) AS total_instances, PRI_KEY
	FROM "MY_TABLE"
GROUP BY FIRST_COL, SEC_COL, THIRD_COL, FOURTH_COL, FIFTH_COL
ORDER BY PRI_KEY

But this gives me an error about data could not be loaded, not in aggregate function.

If I get rid of the Primary key from the Select statement and order by the columns, I don’t get an error, but the results are obvious and not what I’m looking for.

Any and all help would be greatly appreciated.
Thank you in advance.

#2

You will need a special field, not all fields in COUNT(). Might be COUNT(PRI_KEY), because PRI_KEY is there for every row and never NULL. And you haven’t named PRI_KEY in GROUP BY:

SELECT FIRST_COL, SEC_COL, THIRD_COL, FOURTH_COL, FIFTH_COL, COUNT(PRI_KEY) AS total_instances
	FROM "MY_TABLE"
GROUP BY FIRST_COL, SEC_COL, THIRD_COL, FOURTH_COL, FIFTH_COL

Might be you need different queries for every field:

SELECT FIRST_COL, COUNT(PRI_KEY) AS total_instances
	FROM "MY_TABLE"
GROUP BY FIRST_COL
#3

For me this looks like a 5 out of 75 lottery (your relative independend column being the mega-ball) - but just guessing.
.
If I’m right you wish to count numbers over all five columns? Then you have to create a UNION first with
value1, primary-key
value2, primary-key
…

Then you may GROUP BY value and add the COUNT.

#4

@Wanderer Do you mean something like this?

SELECT "INT_NUMBER", COUNT( "INT_NUMBER" ) "COUNT_N" FROM ( 
   SELECT "FIRST_COL" "INT_NUMBER", "PRI_KEY" FROM "MY_TABLE" 
   UNION 
   SELECT "SEC_COL", "PRI_KEY" FROM "MY_TABLE" 
   UNION 
   SELECT "THIRD_COL", "PRI_KEY" FROM "MY_TABLE" 
   UNION 
   SELECT "FOURTH_COL", "PRI_KEY" FROM "MY_TABLE" 
   UNION 
   SELECT "FIFTH_COL", "PRI_KEY" FROM "MY_TABLE" ) 
GROUP BY "INT_NUMBER"
#5

@JohnSUN
Yes.

#6

Ok guys, not sure I completely understand, but will try your suggestions in a few days, this is not a paid project, so it has to be in my spare time. But I’m going out of town for a couple of days and won’t get to try them right away. I’ll post back once I’ve tried.

#7

Not a mega-ball Wanderer, but something similar. I’m thinking Union All, because I don’t want distinct count?

#8

There was just a recent discussion on a topic with a very similar data set. And the “mega-ball” also appeared there

image
image1050×269 48.3 KB

#9

@JohnSUN , where does “INT_NUMBER” come from?

#10

The UNION definitely did the trick, thank you for that. I would like to understand what I’m doing, if you don’t mind educating me a bit. Where does the “INT_NUMBER” come from?
And why when I throw a line in like

WHERE PRI_KEY > 444

this causes my
GROUP BY “INT_NUMBER” to be out of order?

#11

This is an alias, but the keyword AS is often omitted, you may note the missing comma

so here the column in the result-set is renamed to INT_NUMBER, but you may use any name you like.

1 Like
#12

Where in the query are you adding this condition? I see at least six places where you can put this WHERE without breaking the query syntax and getting 6 different results

#13

@JohnSUN
I was putting it between the end of the last Select statement and the ‘group by’ clause.
I solved my problem by just putting an order by statement in.
I do truly appreciate all the help, and the explanations. I took Programming at the local community college, but that only included a couple of classes on SQL programming in general, and the majority of the classes they taught were different various languages from .net to java etc… and we pretty much learned how to say “Hello World” in multiple languages. I’m fascinated by database, hence my reason for playing around with this.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.