|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 10th, 2004, 05:11 AM
|
|||
|
|||
|
Accumulating Data derived from COUNT functions
Hi all,
I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row. For example, if I had the following... ------------------------------------------------------ name - car - van - bus - total ------------------------------------------------------ Terry - 55 - 34 - 12 - 101 John - 01 - 23 - 05 - 029 etc etc ------------------------------------------------------ Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus). Now I have tried the following SQL, but to no avail  Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2)) FROM planvehicles AS pveh ... and I also tried... SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van) FROM planvehicles AS pveh Thanks Tryst
__________________
Tryst
|
|
#2
November 10th, 2004, 06:44 AM
|
|||
|
|||
|
With a little modification I think I have come up with an SQL Script that can do this. It is as follows...
Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, ,(SELECT SUM(name1) + COUNT(name2) FROM planvehicles WHERE id = pveh.id) AS [Total] FROM planvehicles AS pveh I have used a sub SELECT query to add the values of the required columns for the current row and have then displayed it in the end column for each row. To get only the values for the current row I have used the WHERE 'clause id = pveh.id'. There is one problem though  How can I get the sub SELECT to add the numeric value that resides in the column that has been evaluated by the COUNT function 'COUNT(pveh.name2)'?Thanks Tryst
|
|
#3
November 10th, 2004, 07:45 AM
|
||||
|
||||
|
i really don't understand what you're trying to do
how about giving us a few sample rows of the table, then show the results that these rows should produce
|
|
#4
November 10th, 2004, 08:05 AM
|
|||
|
|||
|
I maybe should have mentioned this in may first post, but the following (which is in my first post) is the output of a report...
------------------------------------------------------ name - car - van - bus - total ------------------------------------------------------ Terry - 55 - 34 - 12 - 101 John - 01 - 23 - 05 - 029 etc etc ------------------------------------------------------ I have a table which contains all teh vehicle trips drivers have made. So the report needs to output each driver and the amount of trips they have made for each vehicle (hence the figures in the 'car', 'van', and 'bus' columns). What I then need is to generate a total at the end of each row which adds up each individual drivers trips across all vehicles (hence the 'total' column which adds a total for each driver). In my last post I can up with a sub SELECT query to calculate the total for each individual row, so I'm assuming that is going down teh right route. I can really show you the table it is reallty quite sabby and poorly designed. I hope what I have exaplined here is simple enough for you to understand. Thanks Tryst
|
|
#5
November 10th, 2004, 08:28 AM
|
||||
|
||||
|
Code:
select name
, sum(case when trip='car' then 1 else 0 end ) as car
, sum(case when trip='van' then 1 else 0 end ) as van
, sum(case when trip='bus' then 1 else 0 end ) as bus
, sum(case when trip in ('car','van','bus')
then 1 else 0 end ) as total
from yourtable
|
|
#6
November 10th, 2004, 09:12 AM
|
|||
|
|||
|
Hi r937
Thanks for the reply. I am unable to try the query at the moment, but I thought i'd point out that there is no field called 'Trip' in my database, and the 'Car', 'van', and 'bus' columns are derived via the Count function. Whenever I have tried to embed a COUNT function within a SUM function I have also had errors. Thanks Tryst
|
|
#12
November 10th, 2004, 11:14 AM
|
||||
|
||||
|
Code:
select drivername
, sum(case when left(type,2)='T1' then 1 else 0 end ) as T1
, sum(case when left(type,2)='T2' then 1 else 0 end ) as T2
, sum(case when left(type,2)='ST' then 1 else 0 end ) as ST
, sum(case when left(type,2)='AC' then 1 else 0 end ) as AC
, sum(case when left(type,2)='PR' then 1 else 0 end ) as PR
, sum(case when left(type,2) in ('T1','T2','ST','AC','PR')
then 1 else 0 end ) as total
from DEVELCAD1.meridian.dbo.planvehicles
group
by drivername
|
|
#13
November 11th, 2004, 02:57 AM
|
|||
|
|||
|
Brilliant stuff, r937. Its exactly how I wanted it!!
 You truely are agod at SQL. I did attempt my own version but it contained many sub SELECT queries COUNT functions  Its a pity DevShed don't run any of the forum developer/member awards like Sitepoint do, otherwise you'd be in the running on this site too. Thanks Tryst
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Accumulating Data derived from COUNT functions |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
