| View previous topic :: View next topic |
| Author |
Message |
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
 Posted: Tue Feb 19, 2008 5:20 am Post subject: [SOLVED - GREAT LESSON] Query = COUNT |
 |
|
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 |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Feb 19, 2008 7:51 am Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Tue Feb 19, 2008 9:56 am Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Feb 19, 2008 10:35 am Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Tue Feb 19, 2008 11:27 am Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Feb 19, 2008 12:14 pm Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Tue Feb 19, 2008 12:46 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Feb 19, 2008 2:06 pm Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Tue Feb 19, 2008 5:20 pm Post subject: |
|
|
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 |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
|
| Posted: Tue Feb 19, 2008 6:52 pm Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Wed Feb 20, 2008 3:55 am Post subject: |
|
|
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 |
|
|
compuwatch
Power User
Joined: 19 Dec 2007
Posts: 65
Location: Charlotte, NC
|
| Posted: Thu Feb 21, 2008 1:09 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
