Page 1 of 1

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

PostPosted: Fri May 02, 2014 6:40 pm
by dreamquartz
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

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

PostPosted: Fri May 02, 2014 6:55 pm
by MTP
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" )

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

PostPosted: Sat May 03, 2014 12:09 am
by eremmel
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.

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

PostPosted: Tue May 06, 2014 7:22 pm
by dreamquartz
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

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

PostPosted: Tue May 06, 2014 10:04 pm
by Villeroy
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

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

PostPosted: Thu May 08, 2014 5:03 pm
by dreamquartz
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.

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

PostPosted: Thu May 08, 2014 7:23 pm
by Villeroy
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.

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

PostPosted: Fri May 09, 2014 5:24 am
by dreamquartz
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

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

PostPosted: Fri May 30, 2014 6:34 am
by dreamquartz
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

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

PostPosted: Fri May 30, 2014 9:26 am
by eremmel
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

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

PostPosted: Fri May 30, 2014 4:00 pm
by dreamquartz
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.

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

PostPosted: Fri May 30, 2014 6:37 pm
by eremmel
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).

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

PostPosted: Sat May 31, 2014 12:51 am
by dreamquartz
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

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

PostPosted: Sat May 31, 2014 10:15 pm
by eremmel
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.

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

PostPosted: Mon Jun 02, 2014 5:41 pm
by dreamquartz
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.

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

PostPosted: Tue Jun 03, 2014 9:13 am
by eremmel
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

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

PostPosted: Wed Jun 04, 2014 8:30 am
by dreamquartz
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

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

PostPosted: Wed Jun 11, 2014 6:39 am
by dreamquartz
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