[SOLVED] Working with Countif() or an equivalent

[K-Rad]

Hello fellow forum users!

I am trying to put together a database which allows me to store information about clients, their orders and to generate queries and reports which will give me useful totals, counts, etc.

Currently I am having a problem with generating a query which will give me a total for the different type of group photos.

The idea is, I have a variety of different students across a variety of different year levels. They each order (or don't!) from a range of offered photographic packages. In this instance I am interested in calculating how many group photos from each year level and the different interest groups are required so I can send the order to the printers.

I have a TABLE: StudentInfo
I have relevant fields: Year, Class, VP1, VP2, VP3, VP7, VP8, VP9, ClassGrp, GRPother
(I've only included the fields required for the query/report. Other than Year & Class, those fields are integers containing how many of each product were ordered by the different students, each contains)

Essentially I am trying to get a total of how many class groups are needed in total (VP1;9+ClassGrp) and how many GRPother are needed. I think I have the class groups sorted with the following code:

SELECT "Year" AS "Year Level", "Class" AS "Class", SUM( "ClassGrp" ) AS "Laminated Class Photo", SUM( "VP1" + "VP2" + "VP3" + "VP7" + "VP8" + "VP9" + "ClassGrp" ) AS "Group Total"
FROM "StudentInfo"
WHERE ( "Group Total" > 0 )
GROUP BY "Year", "Class"
ORDER BY "Year Level" ASC, "Class" ASC

However I am not sure how I would go about adding to this code to make a list of the different totals for 'GRPother'. Basically students can be in a bunch of different, non-class groups, such as school captains, choir, etc. These will be entered in manually as text because there is so much variety in groups from school to school and I am just not clever enough to work out how to create a dropdown menu with all the possible alternatives!

So, any advice, initially on whether there is anything glaringly and obviously incorrect with the code I have provided, and secondly on how to go about including a way to sort these text entries so I have a count next to each of them would be super appreciated!

Apologies for the wall of text, I figured more detail provided the better my chances of nailing it properly.

EDIT: On further inspection it would appear the code I posted won't work. So I am not sure how to get a grand total of the totals of each of the fields listed in the brackets. Do I need to do an individual SUM for each (VP1, VP2, etc), giving them a unique alias, then do a TOTALSUM which adds all of these aliases? Sorry for being so clueless!

[Sliderule]

K-Rad:

You said:

[K-Rad]

Hi Sliderule, thanks for your help!

Syntactically your code works perfectly and I can see where I have confused the WHERE with the HAVING however despite the syntax being correct when I run it (with some dummy data in the relevant table) it shows no result. I have another query which just pulls up what was ordered by each people and it shows that the information is there and accessible, it just doesn't seem to come up in this latest query you have helped me with. Do you have any hot tips on how I can add the 'GRPother' into this code? It needs to find a total of all of the different string inputs for 'GRPother' but I can't think of a way to go about it, making me regret not paying more attention to programming in high school all those years ago!

Also I am trying to put your advice relating to your previous post into use for a slightly more complicated piece of code which essentially does the same as above but takes a group of 12 different fields and groups them into 3 totals and sorts them by the year & class. Currently I am getting a syntax error and I am not sure but I am guessing it is in relation to my brave foray into the world of multiple OR clauses...

SELECT
"YearClass" AS "Year & Class",
"Surname" AS "Surname",
"FirstName" AS "First Name",
"IMG#" AS "IMG#",
"Fam1" AS "Mini FP",
"Fam2" AS "Stand FP",
"Fam3" AS "Mega FP",
SUM("VP1" + "VP4" + "VP7" + "Port1") AS "Port Mini",
SUM("VP2" + "VP5" + "VP8" + "Port2") AS "Port Stand",
SUM("VP3" + "VP6" + "VP9" + "Port3") AS "Port Mega"
FROM "StudentInfo"

GROUP BY "YearClass"

HAVING SUM("Port Mini") < 0 OR
SUM("Port Stand") < 0 OR
SUM("Port Mega") < 0

ORDER BY "Year & Class" ASC, "Surname" ASC, "First Name" ASC

Thanks for all your help so far and sorry if I am asking for too much! I didn't close this threat as solved and started another one as I thought it might be easiest to continue working with all of the previous information (table names, etc) in one place. Let me know if this is a bad idea and I will mark as solved and start a new thread!
_________________
What a tragic waste of potential.

[Sliderule]

I have no idea what you are talking about. You said:

[K-Rad]

Thanks again for your patience and help Sliderule, I should have reposted what I wrote about 'GRPother' from my original post.

Essentially: I am not sure how I would go about adding to this code to make a list of the different totals for 'GRPother'. Basically students can be in a bunch of different, non-class groups, such as school captains, choir, etc. These will be entered in manually as text because there is so much variety in groups from school to school and I am just not clever enough to work out how to create a dropdown menu with all the possible alternatives! I need to make it so that it gives me the total count of 'choice', the total 'school captains', etc and shows me what the count is for.

As regards your most previous post, unfortunately it is still returning no result despite being syntactically sound and accepted by the SQL editor. I double checked the data and for instance 'ClassGrp' has 3 instances over 2 different 'Class' but still comes up as a blank field as does 'Laminated Class Photo" which should just be a sum of those 3 instances. I can't think of anything else to tell you which would help sorry!
_________________
What a tragic waste of potential.

[Sliderule]

In the code you provided above, you indicated that it was giving you an error. But, that is 'simple' . . . when you know how.

Below, you will note,. that, each column ( field ) visible ( returned - displayed ) in the return ( SELECT clause ) must EITHER have an AGGREGATE funcion as listed below:

1. SUM
   
2. COUNT
   
3. AVG
   
4. MIN
   
5. MAX
   
6. STDDEV_POP
   
7. STDDEV_SAMP
   
8. VARIANCE_POP
   
9. VARIANCE_SAMP

OR, be a included in the GROUP BY clause. in your code above, you did NOT include "Surname" NOR "FirstName" NOR "IMG#" NOR "Fam1" NOR "Fam2" NOR "Fam3" in the GROUP BY clause, since they are used to tell the database engine you want to use these fields to make them UNIQUE ( DISTINCT ) in the Group that will be 'calculated'.

[Sliderule]

Also, please try the followin Query . . . compared to the FIRST one I wrote at the top.

The difference, since, I do NOT know your database . . . I suspect . . . you may have some NULL ( UNDEFINED ) values. This means, that, the database, when doing calculations, when a PART of the calculation is ( UNDEFINED ) or NULL, it will return a NULL value ( or show up as zero. So, the work-around is to use the COALESCE function, which says, if the value is NULL, make it zero for the calculation . . . like below .

[K-Rad]

Thanks a lot Sliderule, you're a genius!

Both of the codes you provided worked, however the longer one still gave me the problem of not showing data for the SUM(a+b+c) fields. So, using the capability for learning which sets man apart from mollusks, I took your advice regarding the null values and entered in COALESCE functions for each and it worked a treat! Thanks a lot

I am still not 100% sure about the GROUP BY thing, but if I take one thing from it [EDIT:] I had it backwards. IF I SELECT/call something and do NOT use it in an aggregate function, then I must GROUP BY in order to let the DB know to use it.

The only thing I am still unsure about for this particular topic is how to find a total count of items entered as strings. So if for example in my 'GRPother' field, I have 2 entries as 'choir', 1 entry as 'sports captain', and 2 entries as 'school leader', how do I go about getting these totals added to the other totals?
_________________
What a tragic waste of potential.

[Sliderule]

[K-Rad]

Thanks for the persistence Sliderule!

To be honest, I am not even sure what SQL to use as I am not sure of exactly how I want to try to do this. Basically I have a bunch of numerical fields, which are what we have been working towards getting the total for, but then I also have a field which is text and allows the user to input a whole bunch of different values which change from client to clience, such as 'choice', 'school captains', 'team leader', etc, etc.

So for a simplified example, omitting the numerical fields we just dealt with, I have:

Class & Year | Name | GRPother |
12A | Aaron | Choir |
12A | Zeke | Choir |
12A | Josh | Band |
12B | Monty | Band |
12B | Elvis Choir |
12B | John | Band |

I want to add to the previous code:

SELECT
"Year" AS "Year Level",
"Class" AS "Class",
SUM( COALESCE("ClassGrp",0) ) AS "Laminated Class Photo",
SUM( COALESCE("VP1",0) + COALESCE("VP2",0) + COALESCE("VP3",0) + COALESCE("VP7",0) + COALESCE("VP8",0) + COALESCE("VP9",0) + COALESCE("ClassGrp",0) ) AS "Group Total"

FROM "StudentInfo"

GROUP BY "Year Level", "Class"

HAVING SUM( COALESCE("VP1",0) + COALESCE("VP2",0) + COALESCE("VP3",0) + COALESCE("VP7",0) + COALESCE("VP8",0) + COALESCE("VP9",0) + COALESCE("ClassGrp",0) ) > 0

ORDER BY "Year Level" ASC,
"Class" ASC

I want to know whether it is possible to add to this code so I will have the following kind of resulting headings with totals below:

Year | Class | Laminated Class Photo | Group Total | Choir| Band |

Sorry if I am not making enough sense, it is doing my head in trying to think how best to do it whilst explaining it also.
_________________
What a tragic waste of potential.

[Sliderule]

OK.

After working / resolving this one last issue of yours, I will have to insist that you mark this forum post as [Solved] . . . per instructions at the bottom of my posts . . . and . . . any other issues with your database be posted in new forum post. You see, your 'original' questions, hopefully, will now all be solved.

I suspect, that the following SQL Query will meet your needs, but, of course, you will have to 'test' it.

[Sliderule]

Just like to post a few more additional thoughts.

A database, and, its definition, is like a skyscraper ( building ).

The entire database is only as strong as the foundation, and, that foundation is the database definition.

A database, CAN be built,so, NULL ( undefined ) values do NOT exist ( therefore, in your example, the use of COALESCE is NOT needed ). This is done by defining DEFAULT values. For example:

1. In the case of a Boolean field, it default to FALSE rather than NULL
   
2. In the case of a TEXT field, it default to '', rather than NULL ( that is a string with a length of zero )
   
3. In the case of a Numeric/Integer/Decimal field, it default to 0, rather than NULL
   
4. In the case of a Date field, it default to CURRENT_DATE, rather than NULL
   
5. In the case of a Time field, it default to CURRENT_TIME rather than NULL
   
6. In the case of a TimeStamp field, it default to CURRENT_TIMESTAMP rather than NULL

Also, it is possible, to 'modify' the above, once, by running a Query, to create the necessary commands to change the database.

Also, I strongly believe it is important, to design your database ( tables ) in a NORMALIZED way, to make data extraction 'logical', rather than, as too many folks proceed, by creating tables that 'look like' a spreadsheet.

Sliderule

[K-Rad]

Thanks a lot for your help Sliderule, I have marked the thread as solved. I have a further problem which is an extension of this one but I shall create a new thread for it!
_________________
What a tragic waste of potential.