Multiple select statement

[banks]

Hi,

At the moment i have 13 seperate statements selecting a count of records depending on the criteria dates. A few are shown below:-

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  9 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  10 AND impactId = 1

SELECT count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  11 AND impactId = 1

What i'd like to do is combine these statements into one which can be run, and name each result with an alias, such as period1, period2, period3...

I have tried to code it but am unsure:-

SELECT count(impactID), select count(impactID) as period1 FROM tblBaseData WHERE iyear =  2005 AND iperiod =  9 AND impactId = 1 AND supplierId = 1 as period2 iyear =  2005 AND iperiod =  9 AND impactId = 1 AND supplierId = 1

Many thanks,

Alex

 

[kulrom]

I noticed that impactId is always one and that maybe you want to select data where iperiod is value between 1 and 13 so it would be logic if you use between condition.
i.e.

SELECT count(ImpactID) as Something FROM tblBaseData WHERE iperiod BETWEEN 1AND 13 AND impactId = 1

Of course there is also a way to select all this data in a single statement even if you select data out of some logical range. But 1st check the above query ... i beleive it may be helpful for you

 

[cjard]

What you need to do is use a PIVOT query.. I never wrote one but i know it can be used to do your requirement. Take a look at them.

Incidentally, this is the query set you want to pivot:

SELECT
"period" & iPeriod as period,
count(*)
FROM
tblBaseData
WHERE
iYear = 2005 AND impactId = 1
GROUP BY
iPeriod


It will produce:

period1, 1000
period2, 2314
period3, 9831

etc.. the first num being the period, the second the number of impacts in that period (this is support call logging?)

now you pivot this round using a PIVOT so it becomes:

period1, period2, period3 ...
1000, 2314, 9831 ...


Good luck!