CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Abhinav Singh
Abhinav Singh

Reputation: 35

SQL Query to find Records companywise

I want a query that will display rows of those COMPANIES who are giving theft cover and key loss Having status is yes

See the Sample Data below :

   addon        status  amt1    amt2    company
    theft cover yes      7       7      comp1
    key loss    yes      2       2      comp1
    theft cover no      NULL    NULL    comp2
    key loss    yes      2      33      comp2
    key loss    yes      1       1      comp3
    theft cover yes     12      22      comp3
    theft cover yes     11      22      comp4
    key loss    no      NULL    NULL    comp4
    theft cover yes     22      55      comp5
    key loss    yes     33      44      comp5

SELECT     addon, status, amt1, amt2, company
FROM         test
WHERE     (addon = 'theft cover' OR
                      addon = 'key loss') AND (status = 'yes')

Please help me above query not working

and the output should that i want is below :

 addon       status  amt1    amt2    company
    theft cover yes      7       7      comp1
    key loss    yes      2       2      comp1   
    key loss    yes      1       1      comp3
    theft cover yes     12      22      comp3   
    theft cover yes     22      55      comp5
    key loss    yes     33      44      comp5

Upvotes: 0

Views: 211

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Your logic appears to be companies that have no "no" status, not just the ones that have yesses. One method is to use aggregation with a having clause:

SELECT addon, status, amt1, amt2, company
FROM test t
WHERE addon in ('theft cover', 'key loss') AND
      NOT EXISTS (SELECT 1
                  FROM test t2
                  WHERE t2.company = t.company AND
                        t2.addon in ('theft cover', 'key loss') AND
                        t2.status <> 'yes'
                 );

Actually, a simpler way to express this uses window functions. I think this does what you want:

SELECT addon, status, amt1, amt2, company
FROM (SELECT addon, status, amt1, amt2, company,
             COUNT(*) OVER (PARTITION BY company) as cnt
      FROM test t
      WHERE addon in ('theft cover', 'key loss') AND status = 'yes'
     ) t
WHERE cnt = 2;

At least, this works for the sample data in your question.

Upvotes: 0

David Rushton
David Rushton

Reputation: 5030

You could use a combination of GROUP BY and HAVING. GROUP BY will return one row for each company in your table. HAVING can be used as a filter, allowing only companies with a addon count of 2 into the final result.

If you haven't used the HAVING clause before see MSDN for more. 

/* Declare a var to hold the sample data.
 */
DECLARE @SampleData TABLE
    (
        addon        VARCHAR(50),
        [status]        VARCHAR(3),
        amt1        INT,
        amt2        INT,
        company        VARCHAR(50)
    )
;

/* Populate sample data var.
 */
INSERT INTO @SampleData 
    (
        addon,
        [status],
        amt1,
        amt2,
        company
    )
VALUES
    ('theft cover', 'yes', 7, 7, 'comp1'),
    ('key loss', 'yes', 2, 2, 'comp1'),
    ('theft cover', 'no', NULL, NULL, 'comp2'),
    ('key loss', 'yes', 2, 33, 'comp2'),
    ('key loss', 'yes', 1, 1, 'comp3'),
    ('theft cover', 'yes', 12, 22, 'comp3'),
    ('theft cover', 'yes', 11, 22, 'comp4'),
    ('key loss', 'no', NULL, NULL, 'comp4'),
    ('theft cover', 'yes', 22, 55, 'comp5'),
    ('key loss', 'yes', 33, 44, 'comp5')
;

/* Using the HAVING clause I can count the number of addon types
 * each row has.  Any with 2 must have both theft cover and 
 * key loss.
 */
SELECT
    company,
    COUNT(DISTINCT addon) AS addon_count
FROM
    @SampleData
WHERE
    [status] = 'yes'
    AND addon IN ('theft cover', 'key loss')
GROUP BY
    company
HAVING
    COUNT(DISTINCT addon) = 2
;

Upvotes: 0

Chennakrishna
Chennakrishna

Reputation: 181

SELECT addon, status, amt1, amt2, company FROM test WHERE addon in('theft cover', 'key loss') AND (status = 'yes') GROUP BY company, addon, amt1, amt2, company

if you find any duplicates it can be achieve using above query.

Upvotes: 0

SMA
SMA

Reputation: 37023

Use Group BY clause to group your records by company and addon and sum all your amounts (only if you have multiple records which is not in your sample data) like:

SELECT     addon, status, SUM(amt1), SUM(amt2), company
FROM         test
WHERE     (addon = 'theft cover' OR
                  addon = 'key loss') AND (status = 'yes')
GROUP BY company, addon

Upvotes: 2

Related Questions