SQL Server – Return Only Rows Where Count >= 1

sql serversql-server-2008-r2t-sql

This is my data set-up, I am needing a way to return only itemsnames and itemonhand where the count of itemonhand is >= 1

I tried simply adding an OR statement to the query, but that is returning items that have a 0 count.

  Declare @TableTest Table (dealerID int, itemname varchar(100), itemonhand int, itemname2 varchar(100), itemonhand2 int, itemname3 varchar(100), itemonhand3 int)
  INSERT INTO @TableTest (dealerID, itemname, itemonhand, itemname2, itemonhand2, itemname3, itemonhand3) VALUES
  (1, 'ball', 0, 'hat', 1, 'sock', 0)
  ,(2, 'ball', 0, 'hat', 0, 'sock', 1)

  Select * FROM @TableTest
  where itemonhand2 = '1'
  OR itemonhand3 = '1'

My desired result set is

1  hat  1
2  sock 1

I just want one row for each dealerID with all items returned on that one row.

Best Answer

This returns your results and is more extensible for more items. It has been edited to return multiple items per dealer.

DECLARE @TableTest Table (dealerID int, itemname varchar(100), itemonhand int)
INSERT INTO @TableTest (dealerID, itemname, itemonhand) VALUES
(1,'ball',0),
(1,'hat',1),
(1,'sock',0),
(1,'bees',1),
(2,'ball',0),
(2,'hat',0),
(2,'sock',1);


SELECT dealerID,
stock = STUFF (
            (SELECT ',' + itemname  FROM @TableTest as t1 WHERE t1.itemonhand = 1 and t1.dealerID = t2.dealerID FOR XML PATH ('')), 1,1, '' 
            )
FROM @TableTest as t2
GROUP BY dealerID

Related Solutions

SQL Server Count Data – Counting Data on a Third Table Through Link

Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l 
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d 
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE 
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.

SQL Query to Return Data from MAX(ID)

You can use a common table expression (cte).

;with cte as (
select
     row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one
)
select 
    t1.id
    ,t3.bbid
    ,t3.indate
    ,t3.st
    ,t1.val
from
    #One as t1
join #Two as t3
    on t1.bbid = t3.bbid
join cte
    on cte.id = t1.id
    and cte.rn = 1
order by t1.id desc

Best Answer

Related Solutions

SQL Server Count Data – Counting Data on a Third Table Through Link

SQL Query to Return Data from MAX(ID)

Related Question