|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
|
|
#1
November 27th, 2008, 06:12 AM
|
|||
|
|||
|
Help constructing a query
Hi there,
I have the following table of data. type | FP | IP | WP | Code --------------------------------- retail | 100 | 200 | 0 | CUC retail | 1000| 0 | 200 | CUC used | 100 | 200 | 400 | ASC retail | 0 | 200 | 400 | CUC used | 100 | 200 | 0 | CUC What I would like to see is the following type | FP | IP | WP | Code --------------------------------- retail | 2 | 2 | 2 | CUC used | 0 | 1 | 1 | CUC used | 1 | 1 | 1 | ASC So basically if there is a value greater than 0 in FP, IP, and WP it should add to a count. It should be grouped by code and type. I can't figure out if this is even possible in one go or not? Help  Richard
|
|
#2
November 28th, 2008, 02:44 PM
|
||||
|
||||
|
If you are using MS Access the query looks like this:
SELECT DISTINCT Type, DCount("FP","<tableName>","Type = """ & Type & """ AND Code = """ & code & """ AND FP > 0") AS FPCnt, DCount("IP","<tableName>","Type = """ & Type & """ AND Code = """ & code & """ AND IP > 0") AS IPCnt, DCount("WP","<tableName>","Type = """ & Type & """ AND Code = """ & code & """ AND WP > 0") AS WPCnt, Code FROM <tableName>; I'm not sure if SQL server has the DCount function or not. Otherwise you need to do something like this: Code:
SELECT DISTINCT <tableName>.Type, FPCnt, IPCnt, WPcnt, <tableName>.Code
FROM ((<tableName>
Left Outer JOIN [SELECT Type, Count(FP) as FPCnt, Code
FROM <tableName>
WHERE FP > 0
GROUP BY Type, Code]. AS qryFP
ON (<tableName>.Code = qryFP.Code) AND (<tableName>.Type = qryFP.Type))
Left Outer JOIN (SELECT Type, Count(IP) as IPCnt, Code
FROM <tableName>
WHERE IP > 0
GROUP BY Type, Code) AS qryIP
ON <tableName>.Type = qryIP.Type AND <tableName>.Code = qryIP.Code)
Left Outer JOIN (SELECT Type, Count(WP) as WPCnt, Code
FROM <tableName>
WHERE WP > 0
GROUP BY Type, Code) AS qryWP
ON <tableName>.Type = qryWP.Type AND <tableName>.Code = qryWP.Code
In either case you will need to replace <tableName> with the name of your table. Have fun!
__________________
P.S. I am looking for work. <grin>.
|
|
#3
November 28th, 2008, 05:09 PM
|
|||
|
|||
|
Quote:
Thanks for this - I actually posted elsewhere as well and some one came back with this answer which worked perfectly. SELECT type, SUM(SIGN(FP)) AS FP, SUM(SIGN(IP)) AS IP, SUM(SIGN(WP)) AS WP, code FROM Table1 GROUP BY type, code
|
|
| Viewing: Dev Articles Community Forums > Databases > General SQL Development > Help constructing a query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
