OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[SOLVED - GREAT LESSON] Query = COUNT

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Tue Feb 19, 2008 5:20 am    Post subject: [SOLVED - GREAT LESSON] Query = COUNT Reply with quote

I am still having a hard time learning how to use count.

The issue is:
I would need the count for several calibers in a LIKE condition...

Caliber 630, 7922, 37521,37524, 7921,

Code:

SELECT COUNT( `Reference` ) AS `Count per Caliber` FROM `swr`.`contractor_orders_view1` WHERE ( ( `Status` = 'W' ) ) HAVING ( ( COUNT( `Reference` ) LIKE '%630%' ) )


As it is now the query returns nothing...
_________________
Kind Regards,

HANS


Last edited by compuwatch on Thu Feb 21, 2008 1:34 pm; edited 5 times in total
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Feb 19, 2008 7:51 am    Post subject: Reply with quote

Hans:

A LIKE clause is only used for text ( character ) data . . . NOT for numeric data. Therefore, your query as written above should not return data that you desire.

If I am understanding your needs, I suspect you want something like this:
Code:
SELECT
   COUNT( `Reference` ) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W' 
  AND `caliber`>= 630

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Tue Feb 19, 2008 9:56 am    Post subject: Reply with quote

Thank you very much for your reply. The thing is the same field (TEXTFIELD) holds more information like job number / name of watch brand / serial number.... LIKE did in a single occasion return the desired number. However threre is so many other calibers from the same company. I would hate to query them each (I did before its just time consuming.) one at the time.
If I understand you correctly LIKE can only be used once there in no way to nest LIKE with AND ... Is it correct that COUNT would only work on numeric fields. Does COUNT count the rows???
Please take you time I do not want to impose on you.

Thanks
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Feb 19, 2008 10:35 am    Post subject: Reply with quote

Hans:

I do NOT have your database . . . and . . . the exact column names . . . but . . . I suspect . . . something like this might work.
Code:
SELECT
   COUNT(*) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W'
  AND `Reference` LIKE '%630%'


In the event you wanted to create a 'Parameter Query' . . . where the user is prompted to input the 'string' to be searched ( while using a LIKE statement ) . . . something like this might work:
Code:
SELECT
   COUNT(*) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W'
  AND `Reference` LIKE :Input_PCT_Reference_PCT

In the above, the user is 'Prompted to input the required %, a Search Criteria, followed by another %.

Also, just as an FYI . . . in the above . . . COUNT(*) will return the number of 'rows' that meet the criteria of the WHERE clause.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Tue Feb 19, 2008 11:27 am    Post subject: Reply with quote

How do you do that? I am a master watchmaker and you are the MASTER QUERY MAKER. Thank you very much. This query did it.
Code:

SELECT
   COUNT(*) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W'
  AND `Reference` LIKE '%630%'


Is there a way to ask in the same query, all other caliber numbers in one call. I understand the PARAMETER query would achieve this one query at the time. Is it possible to as to say...
the last line of the query...
AND `Reference` AND(LIKE '%630%',LIKE '%7922%',LIKE '%37524%') and GROUP it???

If not it is great as it is. as usual you are the MASTER QUERY MAKER! Thanks
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Feb 19, 2008 12:14 pm    Post subject: Reply with quote

Hans:

Perhaps, something like this:
Code:
SELECT
   COUNT(*) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W'
  AND (`Reference` LIKE '%630%'
   OR  `Reference` LIKE '%7922%'
   OR  `Reference` LIKE '%37524%')

I do not think you need add anything with a GROUP clause.

Just as an additional FYI . . . see this post about allowing for MULTIPLE Parameter Prompts, and, a LIKE may be used here.

http://www.oooforum.org/forum/viewtopic.phtml?t=67527

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Tue Feb 19, 2008 12:46 pm    Post subject: Reply with quote

Thanks again, still works great. Just how would I express the same query showing how much of each caliber are at hand. Thats why I was thinking to use the GROUP clause.

Lets say:

630 | 10
37524 | 15
7922 | 3

What happened in the last query was it does count all of them. and returns 28.
If there is a way this would be the Jackpot.
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Feb 19, 2008 2:06 pm    Post subject: Reply with quote

Hans:

Perhaps, you could try something like this:
Code:
SELECT
   `Reference` AS `Reference`,
   COUNT(*) AS `Count per Caliber`
FROM `swr`.`contractor_orders_view1`
WHERE `Status` = 'W'
  AND (`Reference` LIKE '%630%'
   OR  `Reference` LIKE '%7922%'
   OR  `Reference` LIKE '%37524%')
GROUP BY `Reference`


Sliderule
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Tue Feb 19, 2008 5:20 pm    Post subject: Reply with quote

Unfortunately since we use LIKE and there is more in this text field it returns 28 rows with 1. I really don't thinks there is any other option then query each caliber by itself. I appreciate your help as usual and remain,
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Feb 19, 2008 6:52 pm    Post subject: Reply with quote

Hans:

You said:
Hans wrote:
Unfortunately since we use LIKE and there is more in this text field it returns 28 rows with 1

Since the "view" comes from other tables, are you using the correct table in the FROM . . . put another way . . . rather than using the VIEW . . . do you need to either:
  • Create a view that includes, as a separate column the data you want to GROUP BY
  • In the WHERE portion, include something that will return this piece of data, so the GROUP BY will have the required information.
Sliderule
Back to top
View user's profile Send private message
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Wed Feb 20, 2008 3:55 am    Post subject: Reply with quote

I would love to solve this and appreciate your help very much.

I got: (those are all caliber repaired by this company)
Code:

SELECT COUNT( * )
    AS `Count per Caliber`, `contractor_orders_view1`.`Company`
      FROM `swr`.`contractor_orders_view1`
      WHERE ( ( `Status` = 'W' AND `Reference` LIKE '%630%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%37521%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%37524%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%37526%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%30110%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%30710%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%79240%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%79320%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%7922%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%7912%' )
         OR ( `Status` = 'W' AND `Reference` LIKE '%79350%' ) )
   GROUP BY `Company`
HAVING ( ( `Company` LIKE '%IWC%' ) )


I was thinking of using an IF statement to extract calibers in a new column. Still don't know how I could get to the result....

As usual,
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
compuwatch
Power User
Power User


Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC

PostPosted: Thu Feb 21, 2008 1:09 pm    Post subject: Reply with quote

Thanks to SLIDERULE we all can learn and I am specially thankful for this great lession!

Code:
SELECT
    -- One Column for each search criteria AND also the 'COUNT' line
    SUBSTR( `Reference`,  LOCATE('630', `Reference`), LENGTH('630') )      AS "Reference",
    SUBSTR( `Reference`,  LOCATE('37521', `Reference`), LENGTH('37521') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('37524', `Reference`), LENGTH('37524') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('37526', `Reference`), LENGTH('37526') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('30110', `Reference`), LENGTH('30110') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('30710', `Reference`), LENGTH('30710') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('79240', `Reference`), LENGTH('79240') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('79320', `Reference`), LENGTH('79320') )  AS "Reference",
    SUBSTR( `Reference`,  LOCATE('7922', `Reference`), LENGTH('7922') )    AS "Reference",
    SUBSTR( `Reference`,  LOCATE('7912', `Reference`), LENGTH('7912') )    AS "Reference",
    SUBSTR( `Reference`,  LOCATE('79350', `Reference`), LENGTH('79350') )  AS "Reference",
    COUNT( * ) AS `Count per Caliber`
   
-- The VIEW to 'read'
FROM `swr`.`contractor_orders_view1`

-- Below is where the SEARCH CRITERIA is used to only return data from specific Reference
WHERE ( `Company` LIKE '%IWC%'
        AND ( ( `Status` = 'W' AND `Reference` LIKE '%630%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%37521%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%37524%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%37526%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%30110%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%30710%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%79240%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%79320%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%7922%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%7912%' )
         OR   ( `Status` = 'W' AND `Reference` LIKE '%79350%' ) ) )

-- Use this GROUP BY to GROUP records by the same 'criteria' as in WHERE above
GROUP BY SUBSTR( `Reference`,  LOCATE('630',   `Reference`), LENGTH('630') ),
         SUBSTR( `Reference`,  LOCATE('37521', `Reference`), LENGTH('37521') ),
         SUBSTR( `Reference`,  LOCATE('37524', `Reference`), LENGTH('37524') ),
         SUBSTR( `Reference`,  LOCATE('37526', `Reference`), LENGTH('37526') ),
         SUBSTR( `Reference`,  LOCATE('30110', `Reference`), LENGTH('30110') ),
         SUBSTR( `Reference`,  LOCATE('30710', `Reference`), LENGTH('30710') ),
         SUBSTR( `Reference`,  LOCATE('79240', `Reference`), LENGTH('79240') ),
         SUBSTR( `Reference`,  LOCATE('79320', `Reference`), LENGTH('79320') ),
         SUBSTR( `Reference`,  LOCATE('7922',  `Reference`), LENGTH('7922') ),
         SUBSTR( `Reference`,  LOCATE('7912',  `Reference`), LENGTH('7912') ),
         SUBSTR( `Reference`,  LOCATE('79350', `Reference`), LENGTH('79350') )



This is the mother of all queries...... I am speechless! It works and even more so - you are a genius! Thank you thank you thank you.

I will have to paste this in the original request on the board.

You are truly the MASTER QUERY MAKER!

As always,
_________________
Kind Regards,

HANS
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group