SQLTeam.com | Weblogs | Forums

Table overview with total times of measured values


#1

Good afternoon everyone,

I've got a question which I cannot seem to solve at this moment. In my setup I've got multiple voltage sensors which are continuously recording the voltage output. Every time a voltage is being measured by a sensor, the name of the sensor is written in a record (+ time and voltage).

So far so good...

Now I want to have a sort of summary with an overview of the number of times a sensor had a specific voltage measurement. Below an example output which I'm looking for.
Example

I've already tried to work with the count function, problem with this is that it is not clear to me how to create new columns containig only the values of e.g. Sensor1

Can somebody put me in the right direction?

Thanks!


#2

Can you post a sample of your desired output given this input?


#3


#4

I guess you're not showing all the input data. I see only one sample at 110 volts (and none for sensor 2) but two in your desired output

Anyway, start with something like this (change to match your table)

select voltage, sensor, count(*) as observations
from yourtable
group by voltage, sensor

When you get that working for your table, post your code. Then, we'll pivot the results.


#5

as gbritton said, would be nice if you provided more data. also try to do it in this format

create table #tesla(voltage int, Sensor varchar(50), measuredatetime datetime)

insert into #tesla
select 100, 'V-Sensor1', getdate() union all
select 200, 'V-Sensor2', getdate() union all
select 100, 'V-Sensor1', getdate() union all
select 300, 'V-Sensor3', getdate() union all
select 200, 'V-Sensor2', getdate() union all
select 110, 'V-Sensor1', getdate() union all
select 230, 'V-Sensor3', getdate() 



select *
from 
(
	select voltage, sensor, count(*) as observations
	from #tesla
	group by voltage, sensor
) src
pivot
(
  sum(observations)
  for Sensor in ([V-Sensor1], [V-Sensor2], [V-Sensor3])
) piv;



drop table #tesla

otherwise in order to answer your question, we have to do all of this DDL and DML for you.


#6

Hi,

thank you for the replies. I'll continue this thread in the weekend... something with overwork etc :slight_smile:


#7

@yosiasz , your answer was the correct one and helped me a lot! Thank you for the code!

Regards,

Ben