Select group by with multiple AND conditions on the same columns

group byselect

I've an sql table like this:

SID|SERVICE_SID|ATTRIBUTE_SID|VALUE
1    2           5             "VAL1"
2    2           4             "VAL8"
3    2           3             "VAL4"
4    11          5             "VAL1"
5    11          3             "VAL4"

I'd like to group by service_SID where for the same service_sid:

AND (attribute_sid = 5 && Value="VAL1")

AND (attribute_sid = 4 && Value="VAL8")

So, in this case, only a row with service_sid = 2 should be returned.

Is this a query like this even possible? What would it look like?

Best Answer

You can use INTERSECT which is available in both HSQLDB and SQL Server

SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 5 AND Value='VAL1'
INTERSECT
SELECT SERVICE_SID
FROM SQLTable
WHERE attribute_sid = 4 AND Value='VAL8';

You can then join the result of this query with your table and group by SERVICE_SID

Related Solutions

GROUP BY with OR/AND clause

You can generate a flag column for each group that you need to get:

 CREATE OR REPLACE VIEW vw_messages AS
 SELECT id
      , `from`
      , `to`
      , double_1
      , double_2
      , case when double_1 = double_2 
              and `to` = 0
             then 1
        else 0
        end flag_1
      , case when double_1 = 0
              and double_2 = 0
              and `to` = $id
             then 1
        else 0
        end flag_2
      , case when double_1 = 0
              and double_2 = 0
              and `from` = $id
             then 1
        else 0
        end flag_3
 FROM messages

This way you can later get the rows that accomplish either one or any combinations of groups.

Sql-server – Select when group by contains more than one row with the same value

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

Best Answer

Related Solutions

GROUP BY with OR/AND clause

Sql-server – Select when group by contains more than one row with the same value

Related Question