CODEWITHSUNDEEP
sqlsql-server
Redadublex
Redadublex

Reputation: 129

Collecting two queries in one query

Can i collect these two queries in one query ?

INSERT INTO [dbo].[acceuil](libelle,value,categorie,param)
SELECT 'Scom',count(*),'event','month1'
FROM [dbo].[full]
WHERE date_reception BETWEEN @StartDate AND @EndDate and event_class LIKE '%MOM%'

   INSERT INTO [dbo].[acceuil](libelle,value,categorie,param)
SELECT 'Spectrum',count(*),'event','month1'
FROM [dbo].[full]
WHERE date_reception BETWEEN @StartDate AND @EndDate and event_class LIKE '%SPECTRUM%'

the difference between these queries is in (LIKE'%Spectrum%') and (SELECT Spectrum)

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

or in the where clause should basically do what you want:

INSERT INTO [dbo].[acceuil](libelle,value,categorie,param)
    SELECT (CASE WHEN event_class LIKE '%MOM%' THEN 'Scom'
                 WHEN event_class LIKE '%SPECTRUM%' THEN 'Spectrum'
            END), count(*), 'event', 'month1'
    FROM [dbo].[full]
    WHERE date_reception BETWEEN @StartDate AND @EndDate 
          (event_class LIKE '%MOM%' OR event_class LIKE '%SPECTRUM%')
    GROUP BY (CASE WHEN event_class LIKE '%MOM%' THEN 'Scom'
                   WHEN event_class LIKE '%SPECTRUM%' THEN 'Spectrum'
              END);

I realize that you also need a group by to aggregate by the libelle.

EDIT:

Perhaps a simpler way to write the query is:

INSERT INTO [dbo].[acceuil](libelle,value,categorie,param)
    SELECT libelle, count(*), 'event', 'month1'
    FROM (SELECT f.*,
                 (CASE WHEN event_class LIKE '%MOM%' THEN 'Scom'
                       WHEN event_class LIKE '%SPECTRUM%' THEN 'Spectrum'
                  END) as libelle
          FROM [dbo].[full] f
         ) f
    WHERE libelle IS NOT NULL
    GROUP BY libelle;

This makes it easier to add additional values that you might be looking for.

Upvotes: 4

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can use UNION ALL:

    INSERT  INTO [dbo].[acceuil]
    ( libelle ,
      value ,
      categorie ,
      param
    )
    SELECT  'Scom' ,
            COUNT(*) ,
            'event' ,
            'month1'
    FROM    [dbo].[full]
    WHERE   date_reception BETWEEN @StartDate AND @EndDate
            AND event_class LIKE '%MOM%'

    UNION ALL

    SELECT  'Spectrum' ,
            COUNT(*) ,
            'event' ,
            'month1'
    FROM    [dbo].[full]
    WHERE   date_reception BETWEEN @StartDate AND @EndDate
            AND event_class LIKE '%SPECTRUM%'

or

INSERT  INTO [dbo].[acceuil]
        ( libelle ,
          value ,
          categorie ,
          param
        )
        SELECT  CASE WHEN event_class LIKE '%MOM%' THEN 'Scom'
                     WHEN event_class LIKE '%SPECTRUM%' THEN 'Spectrum'
                END ,
                COUNT(*) ,
                'event' ,
                'month1'
        FROM    [dbo].[full]
        WHERE   date_reception BETWEEN @StartDate AND @EndDate
                AND (event_class LIKE '%MOM%' OR event_class LIKE '%SPECTRUM%')

Upvotes: 0

Related Questions