| View previous topic :: View next topic |
| Author |
Message |
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
 Posted: Sun Sep 14, 2008 9:34 am Post subject: List rows that meet count criteria |
 |
|
I know there's probably examples of this somewhere, but I'm not sure how to search for them. I want to list houses that have more than one car with a purchase date of 2005/01/01 or greater.
| Code: | HOUSE# ITEM PURCH-DATE
------ ---- ----------
HOUSE1 SOFA 2003/07/14
HOUSE1 CAR 2004/03/01
HOUSE1 SOFA 2005/08/23
HOUSE1 CAR 2007/01/13
HOUSE1 TV 2007/02/15
HOUSE1 CAR 2008/06/02
HOUSE2 SOFA 2002/05/14
HOUSE2 CAR 2004/08/04
HOUSE2 TV 2007/02/15
HOUSE2 CAR 2008/08/01
HOUSE3 TV 2007/02/15
HOUSE3 CAR 2008/01/24
HOUSE3 SOFA 2008/09/15 |
So, from the example above, I only want to see:
| Code: | HOUSE1 CAR 2007/01/13
HOUSE1 CAR 2008/06/02 |
I appreciate any help with this... |
|
| Back to top |
|
 |
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Sun Sep 14, 2008 10:52 am Post subject: |
|
|
I think I'm halfway there, but I'm running into 2 problems:
1- If I include PURCH_DATE in the SELECT & GROUP BY clauses,
I only display houses (one row) where 2 or more cars have the SAME purchase
date.
2- If I exclude PURCH_DATE from the SELECT & GROUP BY, I get the
correct HOUSE#, but I don't get the multiple rows displayed or the
PURCH_DATE itself.
| Code: | SELECT HOUSE#,
ITEM,
PURCH_DATE
FROM MYTABLE
WHERE PURCH_DATE >= '01/01/2005'
AND ITEM = 'CAR'
GROUP BY HOUSE#,
ITEM,
PURCH_DATE
HAVING COUNT(*) > 1 |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Sep 14, 2008 11:04 am Post subject: |
|
|
tcurrier,
Try this untested sql
| Code: |
SELECT HOUSE#
,ITEM
,PURCH_DATE
FROM TABLE
WHERE HOUSE# IN (SELECT HOUSE#
FROM TABLE
WHERE ITEM = 'CAR'
AND PURCH_DATE >= '01/01/2005'
GROUP BY HOUSE# HAVING COUNT(*) > 1)
AND ITEM = 'CAR'
AND PURCH_DATE >= '01/01/2005'
;
|
Kolusu |
|
| Back to top |
|
|
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Sun Sep 14, 2008 11:22 am Post subject: |
|
|
Your code has been tested, and it works perfectly....
Thanks ! |
|
| Back to top |
|
|
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Sun Sep 14, 2008 12:17 pm Post subject: |
|
|
I'm going to throw in a monkey wrench and add purchase price to the mix. I only want houses where the total purchase price for all cars
purchased 1/1/2005 and greater, is greater than $20,000.00
| Code: | HOUSE# ITEM PURCH-DATE PURCH_PRICE
------ ---- ---------- -----------
HOUSE1 SOFA 2003/07/14 500.00
HOUSE1 CAR 2004/03/01 15000.00
HOUSE1 SOFA 2005/08/23 325.00
HOUSE1 CAR 2007/01/13 7000.00
HOUSE1 TV 2007/02/15 569.00
HOUSE1 CAR 2008/06/02 6000.00
HOUSE2 SOFA 2002/05/14 225.00
HOUSE2 CAR 2004/08/04 16500.00
HOUSE2 TV 2007/02/15 425.00
HOUSE2 CAR 2008/08/01 21000.00
HOUSE3 TV 2007/02/15 750.00
HOUSE3 CAR 2008/01/24 10000.00
HOUSE3 SOFA 2008/09/15 459.00 |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Sun Sep 14, 2008 6:30 pm Post subject: |
|
|
tcurrier,
Try this untested sql. I still assumed that your conditions in the first post are still valid (.ie houses with more than 1 car)
| Code: |
SELECT A.HOUSE#
,A.ITEM
,A.PURCH_DATE
,A.PURCH_PRICE
FROM MY TABLE A
,(SELECT HOUSE# AS HOUSE#
,COUNT(*) AS NUMCARS
,SUM(PURCH_PRICE) AS TOTPRICE
FROM MY TABLE
WHERE ITEM = 'CAR'
AND PURCH_DATE >= '01/01/2005'
GROUP BY HOUSE#) B
WHERE A.HOUSE# = B.HOUSE#
AND A.ITEM = 'CAR'
AND A.PURCH_DATE >= '01/01/2005'
AND B.NUMCARS > 1
AND B.TOTPRICE >= 20000
; |
If the num of cars is not important then just remove the following condition
Hope this helps...
Cheers
Kolusu |
|
| Back to top |
|
|
tcurrier
Intermediate
Joined: 10 Feb 2006
Posts: 188
Topics: 68
|
| Posted: Mon Sep 15, 2008 7:00 am Post subject: |
|
|
| Works great... Thanks again ! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
