CODEWITHSUNDEEP
sql-servert-sqlpivot
FuriousD
FuriousD

Reputation: 119

How can I consolidate results onto one line

I have a query that I'm trying to consolidate onto 1 line using SQL server. It keeps forcing me to group by the Frequency column but I only want to group by the UserId. Can anyone let me know what I'm doing wrong.

CODE:

SELECT userid, frequency,
(select count(frequency) where frequency = 1) AS Onetime,
(select count(frequency) where frequency = 2) AS Daily,
(select count(frequency) where frequency = 3) AS Weekly,
(select count(frequency) where frequency = 4) AS Fortnightly,
(select count(frequency) where frequency = 5) AS Monthly,
(select count(frequency) where frequency = 6) AS Quarterly,
(select count(frequency) where frequency = 7) AS SemiAnnual,
(select count(frequency) where frequency = 8) AS Annual
FROM AppTasks
where userid = '[email protected]'
group by UserID, Frequency

Data:

image

Upvotes: 3

Views: 79

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131563

The original query isn't working because the SELECT statement contains 7 subqueries, each one counting all records with frequencies returned by the outer query, not just the user's.

You can use PIVOT to calculate the counts for specific frequency numbers and display them as columns:

declare @apptasks table (userid nvarchar(20),frequency int)

insert into @apptasks
values
('[email protected]',1),
('[email protected]',2),
('[email protected]',3),
('[email protected]',4),
('[email protected]',5),
('[email protected]',6),
('[email protected]',7),
('[email protected]',7),
('[email protected]',2),
('[email protected]',1),
('[email protected]',1)


select 
    userid, 
    [1] AS Onetime,
    [2] AS Daily,
    [3] AS Weekly,
    [4] AS Fortnightly,
    [5] AS Monthly,
    [6] AS Quarterly,
    [7] AS SemiAnnual
from ( select 
              userid,
              frequency 
       from @apptasks) as source
PIVOT (
    count(frequency) for Frequency in ([1],[2],[3],[4],[5],[6],[7])
) as pvt

The result is 

userid            Onetime   Daily   Weekly  Fortnightly Monthly Quarterly   SemiAnnual
[email protected]  3         2       1       1           1       1           2

PIVOT and UNPIVOT work if you know which values you want to use beforehand. PIVOT essentially calculates the aggregate (COUNT(*)) for the field (Frequency) values in the IN clause and returns them as columns named after the values.

You can omit Frequency values too :

select 
    userid, 
    [1] AS Onetime,
    [2] AS Daily,
    [3] AS Weekly,
    [4] AS Fortnightly,
    [5] AS Monthly
from (select userid,frequency from @apptasks) as source
PIVOT 
(
        count(frequency) for Frequency in ([1],[2],[3],[4],[5])
) as pvt

Upvotes: 1

gotqn
gotqn

Reputation: 43646

Could you try this?

SELECT userid,
SUM(CASE WHEN frequency = 1 THEN 1 ELSE 0 END) AS Onetime,
SUM(CASE WHEN frequency = 2 THEN 1 ELSE 0 END) AS Daily,
SUM(CASE WHEN frequency = 3 THEN 1 ELSE 0 END) AS Weekly,
SUM(CASE WHEN frequency = 4 THEN 1 ELSE 0 END) AS Fortnightly,
SUM(CASE WHEN frequency = 5 THEN 1 ELSE 0 END) AS Monthly,
SUM(CASE WHEN frequency = 6 THEN 1 ELSE 0 END) AS Quarterly,
SUM(CASE WHEN frequency = 7 THEN 1 ELSE 0 END) AS SemiAnnual,
FROM AppTasks
where userid = '[email protected]'
group by UserID;

Upvotes: 2

ahmed abdelqader
ahmed abdelqader

Reputation: 3568

simply since you want group by userID only , so remove frequency from Select statment as next:-

SELECT userid,
(select count(frequency) where frequency = 1) AS Onetime,
(select count(frequency) where frequency = 2) AS Daily,
(select count(frequency) where frequency = 3) AS Weekly,
(select count(frequency) where frequency = 4) AS Fortnightly,
(select count(frequency) where frequency = 5) AS Monthly,
(select count(frequency) where frequency = 6) AS Quarterly,
(select count(frequency) where frequency = 7) AS SemiAnnual,
(select count(frequency) where frequency = 8) AS Annual
FROM AppTasks
where userid = '[email protected]'
group by UserID

Upvotes: 0

Related Questions