SQL Server query multiple values in same column

Question

I am very new to SQL so please bear with me! I am trying to write a T-SQL query (SSMS) to retrieve two different maximum "Quantity" values from the same "Name" from a database, call it "meter_data", in the format below:

Name    Date       Time   Value   Quantity
------------------------------------------
Meter1  29-08-17   19:00    2.1   Amps
Meter1  29-08-17   19:30    2.5   Amps
Meter1  29-08-17   20:00    2.2   Amps
Meter1  29-08-17   19:00    231   Volts
Meter1  29-08-17   19:30    232   Volts
Meter1  29-08-17   20:00    235   Volts
Meter2  29-08-17   19:00    16.1  Amps
Meter2  29-08-17   19:30    17    Amps
Meter2  29-08-17   20:00    17.1  Amps
Meter2  29-08-17   19:00    415   Volts
Meter2  29-08-17   19:30    413   Volts
Meter2  29-08-17   20:00    412   Volts

etc... etc... etc... etc...

The result of the query should return the following data:

Meter 1
Amps 2.5
Volts 235

Here is my query:

SELECT MAX(Value)
FROM meter_data
WHERE Name = 'Meter1' 
  AND (Quantity = 'Amps') 
  AND (Quantity = 'Volts')

The result of the above is:

Meter1
NULL

The query is obviously incorrect, as it ANDs the two Quantity together and returns a NULL value. I have tried several different ways to achieve this (INNER JOIN, IN, INTERSECT...) but for the life of me I cannot figure out where I'm going wrong. Should there be another WHERE clause?

Not asking for a free bit of code but if someone can point me in the right direction I'll be really grateful!

Thanks.

You have to use group by: SELECT Name, Quantity, MAX(Value) FROM meter_data GROUP BY Name, Quantity — Marcelo
– Marcelo, Commented Aug 29, 2017 at 21:42
@Marcelo you have the right answer, but you don't need the name do you? select max(value) as max, quantity from meter_data group by quantity; — Mark Giaconia
– Mark Giaconia, Commented Aug 29, 2017 at 23:36
No, I don't need the name in the result, just the max for 'Amps' and 'Volts' for a particular meter. — EnergyAuto
– EnergyAuto, Commented Aug 29, 2017 at 23:40
You need the name only if you want the Max value per meter(Meter1,Meter2). If you want the overall value, you do not need the name. — Marcelo
– Marcelo, Commented Aug 29, 2017 at 23:49

Mark Giaconia · Accepted Answer · 2017-08-29 21:46:53Z

1

You could do this a few different ways, here's a shot at it. One way is like this, just using a union to combine into your result. (The CTE is just to mimic your table)

with x as
(
select 'Meter1' AS METER,  2.1 AS AMOUNT ,'Amps' AS PTYPE
union ALL
select'Meter1', 2.5 , 'Volts'
union ALL
select'Meter1',  2.2 , 'Volts'
union ALL
select'Meter1',   231 , 'Volts'
union ALL
select'Meter1',   232 , 'Volts'
union ALL
select'Meter1',  235 , 'Volts'
union ALL
select'Meter2', 16.1 ,'Amps'
union ALL
select'Meter2', 17 ,'Amps'
union ALL
select'Meter2', 17.1 ,'Amps'
union ALL
select'Meter2' , 415 , 'Volts'
union ALL
select'Meter2', 413 , 'Volts'
union ALL
select'Meter2' , 412, 'Volts'

)
SELECT MAX(X.AMOUNT) AS AMOUNT, 'AMPS' FROM X WHERE X.PTYPE='AMPS'
UNION
SELECT MAX(X.AMOUNT) AS AMOUNT, 'VOLTS' FROM X WHERE X.PTYPE='VOLTS'

or you could do it more like a pivot, which would look like this

with x as
(
select 'Meter1' AS METER,  2.1 AS AMOUNT ,'Amps' AS PTYPE
union ALL
select'Meter1', 2.5 , 'Volts'
union ALL
select'Meter1',  2.2 , 'Volts'
union ALL
select'Meter1',   231 , 'Volts'
union ALL
select'Meter1',   232 , 'Volts'
union ALL
select'Meter1',  235 , 'Volts'
union ALL
select'Meter2', 16.1 ,'Amps'
union ALL
select'Meter2', 17 ,'Amps'
union ALL
select'Meter2', 17.1 ,'Amps'
union ALL
select'Meter2' , 415 , 'Volts'
union ALL
select'Meter2', 413 , 'Volts'
union ALL
select'Meter2' , 412, 'Volts'

)
select Max(case when X.PTYPE='AMPS' then amount  end) as 'amps',
Max(case when X.PTYPE='volts' then amount  end) as 'volts'
from x;

Your query isn't working because VOLTS AND AMPS are never in the same row, so the AND is always false.

You could probably do something elegant with OVER(..) partition by windowing stuff if you want to as well, so you might look into that.

HTH

answered Aug 29, 2017 at 21:46

Mark Giaconia

3,9716 gold badges22 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EnergyAuto Over a year ago

Thanks! I'll look into the OVER clause. The tricky thing with UNION ALL is that there will be over 500,000 rows in the database and 2,500 meters ...and growing!

Mark Giaconia Over a year ago

You don't need to do any UNION ALLs, I just did that in my CTE to mimic your data you pasted into the question. You just need to look at the bottom parts of the query

Mark Giaconia Over a year ago

@Marcelo above actually provided the simplest answer, just a regular old group by will do what you want - not sure why I jumped to these complicated approaches . . . :-)

Jason A. Long · Accepted Answer · 2017-08-30 02:32:31Z

Here are two different options...

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    [Name] CHAR(6) NOT NULL,
    [Date Time] DATETIME2(0) NOT NULL,
    [Value] DECIMAL(9,1) NOT NULL ,
    Quantity VARCHAR(5) NOT NULL
    );
SET DATEFORMAT 'DMY';
INSERT #TestData (Name, [Date Time], Value, Quantity) VALUES
    ('Meter1', '29-08-2017 19:00', 2.1, 'Amps'),
    ('Meter1', '29-08-2017 19:30', 2.5, 'Amps'),
    ('Meter1', '29-08-2017 20:00', 2.2, 'Amps'),
    ('Meter1', '29-08-2017 19:00', 231, 'Volts'),
    ('Meter1', '29-08-2017 19:30', 232, 'Volts'),
    ('Meter1', '29-08-2017 20:00', 235, 'Volts'),
    ('Meter2', '29-08-2017 19:00', 16.1, 'Amps'),
    ('Meter2', '29-08-2017 19:30', 17, 'Amps'),
    ('Meter2', '29-08-2017 20:00', 17.1, 'Amps'),
    ('Meter2', '29-08-2017 19:00', 415, 'Volts'),
    ('Meter2', '29-08-2017 19:30', 413, 'Volts'),
    ('Meter2', '29-08-2017 20:00', 412, 'Volts');

--===========================================================

-- 2 rows per meter...
WITH 
    cte_AddRank AS (
        SELECT 
            td.Name, td.[Date Time], td.Value, td.Quantity,
            DR = DENSE_RANK() OVER (PARTITION BY td.Name, td.Quantity ORDER BY td.Value DESC)
        FROM
            #TestData td
        )
SELECT 
    ar.Name, ar.Quantity, ar.Value
FROM
    cte_AddRank ar
WHERE 
    ar.DR = 1;

--================================================

-- 1 row per meter (pivioted)...
SELECT 
    td.Name,
    Amps = MAX(CASE WHEN td.Quantity = 'Amps' THEN td.Value END),
    Volts = MAX(CASE WHEN td.Quantity = 'Volts' THEN td.Value END)
FROM
    #TestData td
GROUP BY
    td.Name;

Results...

-- 2 rows per meter...
Name   Quantity Value
------ -------- ---------------------------------------
Meter1 Amps     2.5
Meter1 Volts    235.0
Meter2 Amps     17.1
Meter2 Volts    415.0

-- 1 row per meter (pivioted)...
Name   Amps                                    Volts
------ --------------------------------------- ---------------------------------------
Meter1 2.5                                     235.0
Meter2 17.1                                    415.0

Collectives™ on Stack Overflow

SQL Server query multiple values in same column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related