[Solved] Issue with COUNT, used in comb. with a "Condition"

Discuss the database features

[Solved] Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Fri May 02, 2014 6:40 pm

Hello All,

I need to add a leading '0' when a COUNT is < 10.
Code: Select all   Expand viewCollapse view
SELECT
    CASE
       WHEN COUNT( "tblServiceUsage"."FKServiceID" ) < 10
       THEN '0' || COUNT( "tblServiceUsage"."FKServiceID" )
       ELSE '' || COUNT( "tblServiceUsage"."FKServiceID" )
    END
AS
    "ServiceUsage"
FROM
    "tblServiceUsage"
GROUP BY
    "FKServiceID"
Gives the following error:
Not a condition in statement [SELECT CASE WHEN COUNT( "tblServiceUsage"."FKServiceID" ) < 10 THEN '0' || COUNT( "tblServiceUsage"."FKServiceID" ) ELSE '' || COUNT( "tblServiceUsage"."FKServiceID" ) END AS "ServiceUsage" FROM "tblServiceUsage" GROUP BY "FKServiceID"]

It appears that the COUNT I am using can not be part of the THEN or the ELSE.
Can someone please explain what I am doing wrong?

Dream
Last edited by dreamquartz on Fri Jul 04, 2014 8:13 pm, edited 3 times in total.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in combination with a "Condition"

Postby MTP » Fri May 02, 2014 6:55 pm

I know the CASE syntax is a little different in HSQL/Base, and I don't have it straight myself; the software might be objecting to the syntax and not the conditional. Maybe try this?
Code: Select all   Expand viewCollapse view
CASEWHEN( COUNT( "tblServiceUsage"."FKServiceID" ) < 10, '0', '' ) || COUNT( "tblServiceUsage"."FKServiceID" )
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Issue with COUNT, used in combination with a "Condition"

Postby eremmel » Sat May 03, 2014 12:09 am

The following expression might be simpler:
RIGHT( '0' || COUNT(....), 2)
Is also more easy to extend when your numbers are getting larger than 2 positions.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Issue with COUNT, used in combination with a "Condition"

Postby dreamquartz » Tue May 06, 2014 7:22 pm

MTP wrote:I know the CASE syntax is a little different in HSQL/Base, and I don't have it straight myself; the software might be objecting to the syntax and not the conditional. Maybe try this?
Code: Select all   Expand viewCollapse view
CASEWHEN( COUNT( "tblServiceUsage"."FKServiceID" ) < 10, '0', '' ) || COUNT( "tblServiceUsage"."FKServiceID" )


Hi MTP,

Thanks for your response.
Your code suggestion made me realize that COUNT could be removed from within the "Condition".
See https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69091#p310342

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Issue with COUNT, used in comb. with a "Conditi

Postby Villeroy » Tue May 06, 2014 10:04 pm

Code: Select all   Expand viewCollapse view
SELECT "FKServiceID",COUNT( "tblServiceUsage"."FKServiceID" ) AS "ServiceUsage"
FROM
    "tblServiceUsage"
GROUP BY
    "FKServiceID"
HAVING COUNT( "tblServiceUsage"."FKServiceID" ) >=10

Lists all "FKServiceID" having a count >=10
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Issue with COUNT, used in comb. with a "Conditi

Postby dreamquartz » Thu May 08, 2014 5:03 pm

Villeroy wrote:
Code: Select all   Expand viewCollapse view
SELECT "FKServiceID",COUNT( "tblServiceUsage"."FKServiceID" ) AS "ServiceUsage"
FROM
    "tblServiceUsage"
GROUP BY
    "FKServiceID"
HAVING COUNT( "tblServiceUsage"."FKServiceID" ) >=10

Lists all "FKServiceID" having a count >=10


Hi Villeroy,

This is much appreciated.
Never used the HAVING option, because I did not know it.
So far I have always used WHERE, but this definitely comes in handy.

I think I did solve my issue (see: https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=69091#p310342).

Dream.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Issue with COUNT, used in comb. with a "Conditi

Postby Villeroy » Thu May 08, 2014 7:23 pm

When aggregating row sets, the statement can have the following keywords in this order:

SELECT
FROM ... JOIN ...
WHERE
GROUP BY
HAVING
ORDER BY

Aggregation shrinks the row sets because you calculate one value from many rows for each group. A sum, count, min, max is derived from many values.
WHERE filters the rows before aggregation.
HAVING filters the rows after aggregation.
GROUP BY is required for all selected fields that are not aggregated by some calculation. The grouped fields make up the categories for which we calculate the values.
Apart from the different types of JOINs this is all you need to know about aggregations.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26967
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Issue with COUNT, used in comb. with a "Conditi

Postby dreamquartz » Fri May 09, 2014 5:24 am

Villeroy wrote:When aggregating row sets, the statement can have the following keywords in this order:

SELECT
FROM ... JOIN ...
WHERE
GROUP BY
HAVING
ORDER BY

Aggregation shrinks the row sets because you calculate one value from many rows for each group. A sum, count, min, max is derived from many values.
WHERE filters the rows before aggregation.
HAVING filters the rows after aggregation.
GROUP BY is required for all selected fields that are not aggregated by some calculation. The grouped fields make up the categories for which we calculate the values.
Apart from the different types of JOINs this is all you need to know about aggregations.


Hi Villeroy,

Wow, thank you.
I really still have much to learn.
These are the things I am looking for.
This gives an insight in how things can be organized.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: [Solved] Issue with COUNT, used in comb. with a "Conditi

Postby dreamquartz » Fri May 30, 2014 6:34 am

Villeroy wrote:When aggregating row sets, the statement can have the following keywords in this order:

SELECT
FROM ... JOIN ...
WHERE
GROUP BY
HAVING
ORDER BY

Aggregation shrinks the row sets because you calculate one value from many rows for each group. A sum, count, min, max is derived from many values.
WHERE filters the rows before aggregation.
HAVING filters the rows after aggregation.
GROUP BY is required for all selected fields that are not aggregated by some calculation. The grouped fields make up the categories for which we calculate the values.
Apart from the different types of JOINs this is all you need to know about aggregations.


Hi Villeroy,

Question: Is there a way to find the i.e. the 7th entry, where there are already more than 7 entries, using these terms?
The way I use Count right now only shows the total amount, but for future reference, I must be able to find a certain entry.

I cannot find anything for that matter.
Please refer to the table "tblServiceUsage" in the attached database.

Dream
Attachments
Company_Cascading_Address_12.odb
(83.67 KiB) Downloaded 64 times
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in comb. with a "Condition"

Postby eremmel » Fri May 30, 2014 9:26 am

As I understand, you like to assign a rank to each FKServiceID based on ServiceUsageID-order. Create a query e.g. named: qServiceUsageRanked with
Code: Select all   Expand viewCollapse view
select X."Rank", c.*
from ( select a.ServiceUsageID, a."FKServiceID", count(*) as "Rank"
   from "tblServiceUsage" a
   left join "tblServiceUsage" b on a."FKServiceID" = b."FKServiceID" and a.ServiceUsageID >= b.ServiceUsageID
   group by a.FKServiceID, a.ServiceUsageID
) X
inner join "tblServiceUsage" c on X.ServiceUsageID = c.ServiceUsageID

Use this query like e.g.:
Code: Select all   Expand viewCollapse view
select * from qServiceUsageRanked where "Rank" = 7
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Fri May 30, 2014 4:00 pm

eremmel wrote:As I understand, you like to assign a rank to each FKServiceID based on ServiceUsageID-order. Create a query e.g. named: qServiceUsageRanked with
Code: Select all   Expand viewCollapse view
select X."Rank", c.*
from ( select a.ServiceUsageID, a."FKServiceID", count(*) as "Rank"
   from "tblServiceUsage" a
   left join "tblServiceUsage" b on a."FKServiceID" = b."FKServiceID" and a.ServiceUsageID >= b.ServiceUsageID
   group by a.FKServiceID, a.ServiceUsageID
) X
inner join "tblServiceUsage" c on X.ServiceUsageID = c.ServiceUsageID

Use this query like e.g.:
Code: Select all   Expand viewCollapse view
select * from qServiceUsageRanked where "Rank" = 7


Hi Eremmel,

This looks promissing.
I will pusue.

Dream

Edit:
Had a look at it and appears to be what I am looking for.
Now a different question: Can you eleborate on the way the query is written. I so not quite understand what you did.

Dream.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in comb. with a "Condition"

Postby eremmel » Fri May 30, 2014 6:37 pm

Now you know why people can make a living with writing SQL.

This is a common pattern for SQL. The idea is that you 'blow up' your result set and then count it. This result set is based on a set of fields such that there is a group-set of fields (G) and a ranking set (the fields that needs to be ordered over each row g out of T(G)) of fields (R). In you example G is one field FKServiceID and R is one field ServiceUsageID.
So you join the table T with it self (aliases t1 and t2) such that for each t1.{g,ri} you add to it all t2.{g,rj} such that t1.g = t2.g and t1.ri >= t2.rj. Assume that we have in T {g,r1}, {g,r2}, {g,r3} and r1 < r2 < r3 we make a 'blown up" set that looks like:
t1.{g,r1}, t2.{g.r1}

t1.{g,r2}, t2.{g.r1}
t1.{g,r2}, t2.{g.r2}

t1.{g,r3}, t2.{g.r1}
t1.{g,r3}, t2.{g.r2}
t1.{g,r3}, t2.{g.r3}

So if we now group this result over t1.{g,r} with a count(*) and forget about the t2. result we end up with
t1.{g,r1,1}
t1.{g,r2,2}
t1.{g,r3,3}

With this result it is time to get the other data from T and that is done with the outer select.
You might need take your time to play with parts of the query.
E.g. the inner query between FROM ( ... ) X. Remove the aggregation and grouping clause and look to the results (take a proper ordering).
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Sat May 31, 2014 12:51 am

eremmel wrote:Now you know why people can make a living with writing SQL.

This is a common pattern for SQL. The idea is that you 'blow up' your result set and then count it. This result set is based on a set of fields such that there is a group-set of fields (G) and a ranking set (the fields that needs to be ordered over each row g out of T(G)) of fields (R). In you example G is one field FKServiceID and R is one field ServiceUsageID.
So you join the table T with it self (aliases t1 and t2) such that for each t1.{g,ri} you add to it all t2.{g,rj} such that t1.g = t2.g and t1.ri >= t2.rj. Assume that we have in T {g,r1}, {g,r2}, {g,r3} and r1 < r2 < r3 we make a 'blown up" set that looks like:
t1.{g,r1}, t2.{g.r1}

t1.{g,r2}, t2.{g.r1}
t1.{g,r2}, t2.{g.r2}

t1.{g,r3}, t2.{g.r1}
t1.{g,r3}, t2.{g.r2}
t1.{g,r3}, t2.{g.r3}

So if we now group this result over t1.{g,r} with a count(*) and forget about the t2. result we end up with
t1.{g,r1,1}
t1.{g,r2,2}
t1.{g,r3,3}

With this result it is time to get the other data from T and that is done with the outer select.
You might need take your time to play with parts of the query.
E.g. the inner query between FROM ( ... ) X. Remove the aggregation and grouping clause and look to the results (take a proper ordering).


Thanks Eremmel,

This definitely helps to get a better understanding.

I have an other request.
Can you please explain where in the query you can filter on i.e. the timestamp?
The places I came up with, based on the order given by Villeroy, does not provide with the resultset I am looking for.
I need to be able to find a record for a specific year. At the beginning of each year the counters should be reset, and the way I came up with is using ProjectDate, StartTime, and Executed.
I need to be able to go back in the table in the future and find a specific record.
The moment that the ProjectDate and the StartTime are entered, and it has been executed, I need to be able to find that specific record, because of the requirements by the Client.
They need to keep paper records (legal requirement) and use a 6-digit stamp.
The stamp holds the 1st 2 digits as year (i.e. 14), the 2nd 2 digits are the last to digits of the ServiceID (i.e. 18) and the last 2 digits are the sequence number for the ProjectNumber, leading to i.e. 141807.
The problem lays in this PN.
I can live with that a PN is entered, but not executed.
There must be however a possibility to get a total count the ones that were/are executed for a specific year.

If you look at my other request for defining the ProjectNumber, you understand what I mean.
The easiest way for me to solve the problem I am facing is to be able to store the result of a Query like Query2, but I can not find a solution for every specific ServiceID that has been entered.

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in comb. with a "Condition"

Postby eremmel » Sat May 31, 2014 10:15 pm

Your description is not very clear to me.
But I think that you are looking to have the query extended with a rank per {year, FKServiceID} to be able to reset the rank count per year. This implies that you have to extend the places where FKServiceID is mentioned and add the year to it. But which date do you take for the year?

If you want to calculate the rank over a subset of the data you need to filter in the inner query on both aliasses a and b because you want to exclude records that are e.g. not executed yet; on alias a because you do not want them in the output and on alias b because you do not want to have them an impact on the Rank.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Mon Jun 02, 2014 5:41 pm

eremmel wrote:Your description is not very clear to me.
But I think that you are looking to have the query extended with a rank per {year, FKServiceID} to be able to reset the rank count per year. This implies that you have to extend the places where FKServiceID is mentioned and add the year to it. But which date do you take for the year?

If you want to calculate the rank over a subset of the data you need to filter in the inner query on both aliasses a and b because you want to exclude records that are e.g. not executed yet; on alias a because you do not want them in the output and on alias b because you do not want to have them an impact on the Rank.


Hi Eremmel,

Thanks for heping me putting my ducks on a row.
The database I use for the examples has a table called tblServiceUsage.
This table provides an example of what the Client has.
If you also look at Query2, you see what happens with the ServiceID from the tblServiceUsage.
The last to digits of the ServiceID are the 3rd and 4th digits of the Project Number, created by the Query2.
The 1st 2 digits of the PN are the last 2 digits of the current year while the last
2 digits of the PN are used for the sequence number.
All of this is based on the requirement by the Client, who wants to stick to a 6 digit coding.
The Client sells a Service (using the ServiceID) and uses that in their accounting system, while all the documents are linked/stamped with the PN, which is related (see above) to the Service they sell.

The Client does not sell just one, but multiple Services to their Customers, and that definitely not in sequence.

The Client has a legal obbligation to keep track on what Services they sell, and how many they sell.
Some of these Services are training programs, and for those programs, they need to be able to identify who took part, and what the outcome of that program was for every individual.
Information about all of the Services must be kept on site at the Client's for a certain set time.
Information must be accumulated and forwarded to the authorities on an annual basis.

All these requirements lead to the following:
Service sold in a certain year.
The PN must be entered in the Project Calendar.
A Project Mager/Instructor needs to be linked to the Service.
A PM/Instructor must be paid.
How many participants, and their results.
Paperwork must de identifiable.
Paperwork must be kept for a certain set period of time.
Information must be forwarded.
Any type of information, related to any Service, needs to be retreivable.

Query2 only provides a total of Services as per date of entry of a Service.
The Client needs to be able to go back in the database and retreive any information. One of the methods is to use the PN.

Here lays the issue for now.
The PN is created via a Query, but what is really needed is some form of counting method on an annual basis for a Service provided, that can be retreived.
The table tblServiceUsage (an idea from DACM), uses the Current TimeStamp as an example of Date and Time. I need to use the ProjectDate, StartTime, and Executed, as the criteria, but that is an easy fix.
This table does hold all the Services entered and executed throughout the years.
The information will be linked to Participants, and must be accounted for.
So those Services that are entered, in a certian year must be traced back.

Hope this long story helps you a little.

Dream.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in comb. with a "Condition"

Postby eremmel » Tue Jun 03, 2014 9:13 am

Dream,

I personal have the opinion that a forum like this is too limited to assist with implementing a complete IT project. Dedicated questions work very well, but improving ones engineering skills needs another podium. It also takes just too much time to try to understand the the needs, translate them into an understandable precise specification and take that into tables, form and queries. The issue is that one need to know the capabilities of the application and SQL to be able to write a specification to some extend; this is like a chicken-egg problem. I think that this is sometimes expressed at the forum by not receiving reactions any more, but I think that would be not nice to do now.

Please consider this not as an offending statement, but as a remark that it is important to a certain experience level for a corresponding complexity and that a forum like will not work out, at least in my opinion. A complicating factor is also that I'm not a native English speaker so I might miss certain aspects.

I'm glad to take simple questions but this takes too much time.

Regards,
Eremmel
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1037
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Wed Jun 04, 2014 8:30 am

eremmel wrote:Dream,

I personal have the opinion that a forum like this is too limited to assist with implementing a complete IT project. Dedicated questions work very well, but improving ones engineering skills needs another podium. It also takes just too much time to try to understand the the needs, translate them into an understandable precise specification and take that into tables, form and queries. The issue is that one need to know the capabilities of the application and SQL to be able to write a specification to some extend; this is like a chicken-egg problem. I think that this is sometimes expressed at the forum by not receiving reactions any more, but I think that would be not nice to do now.

Please consider this not as an offending statement, but as a remark that it is important to a certain experience level for a corresponding complexity and that a forum like will not work out, at least in my opinion. A complicating factor is also that I'm not a native English speaker so I might miss certain aspects.

I'm glad to take simple questions but this takes too much time.

Regards,
Eremmel


Hi Eremmel,

I totally understand what you are saying.
I only repeated in so many words the base of the database structure, I am building.
I just wanted to explain that there are still a couple of problems.
At this point, the issue I am working on is the one I mentioned above.

Of course it is something I need to solve, and I ony hope, by providing as much insight I am allowed to by the Client, I am able to create solutions to issues I come accross.

I am still trying to wrap my head around your solution you provided, and many thanks for that.

I hope you can answer one question for me at this point.
Where/how add a filter for a specific year, as you indicated that that was possible.
I tried it at different locations, but to no avail.

I am missing something, but I simply do not see it.

Again, thank you for your help,

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am

Re: Issue with COUNT, used in comb. with a "Condition"

Postby dreamquartz » Wed Jun 11, 2014 6:39 am

eremmel wrote:As I understand, you like to assign a rank to each FKServiceID based on ServiceUsageID-order. Create a query e.g. named: qServiceUsageRanked with
Code: Select all   Expand viewCollapse view
select X."Rank", c.*
from ( select a.ServiceUsageID, a."FKServiceID", count(*) as "Rank"
   from "tblServiceUsage" a
   left join "tblServiceUsage" b on a."FKServiceID" = b."FKServiceID" and a.ServiceUsageID >= b.ServiceUsageID
   group by a.FKServiceID, a.ServiceUsageID
) X
inner join "tblServiceUsage" c on X.ServiceUsageID = c.ServiceUsageID

Use this query like e.g.:
Code: Select all   Expand viewCollapse view
select * from qServiceUsageRanked where "Rank" = 7


Hi Eremmel,

An update to this issue.
I did not catch your last remark concerning using the Query.
This definitely made me realize that I am able to select information from a Query in a Query.
This provides me with a potential solution for retreiving a record later, as I am looking for this option.

I will keep you updated,

Dream
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 701
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests