CODEWITHSUNDEEP
sqlsql-serverquery-expressions
user1166085
user1166085

Reputation: 627

sql statement that requires 2 distinct

i have a table where there's a user_id and a ticket_type. i want to create a sql statement that will return me the the count of each ticket_type that each user_id generate.

i have come up with this but this involves some hard-coding of ticket_type which i'm not fond of.

select b.user_id, b.TypeA, B.TypeB, (b.TypeA + b.TypeB) As "Total Tickets" from
(select user_id, 
sum(case when ticket_type = '1' then 1 else 0 end) as "TypeA",
sum(case when ticket_type = '2' then 1 else 0 end) as "TypeB"
from 
database
group by user_id
) as b;

is there a better way to do this? it would be really awesome if someone can provide the answer in linq query expression as well.

EDIT Sample Output

User_ID   Type_A   Type_B   Total
user1       3        5         8
user2       1        2         3        
user3       6        8        14

Sample Database Table

DECLARE @Users As TABLE
(
    SN int,
    User_ID char(5),
    Ticket_Type int
)

INSERT INTO @Users VALUES
(1, 'user1', 1),
(2, 'user1', 1),
(3, 'user2', 2),
(4, 'user3', 1),
(5, 'user1', 2),
(6, 'user1', 2),
(7, 'user2', 2),
(8, 'user2', 2)

Upvotes: 2

Views: 103

Answers (3)

Arunprasanth K V
Arunprasanth K V

Reputation: 21931

You can use the following linq query.

 var result= from a in db.somename group a by user_id into b 
    select new { UserId=b.user_id, 
    TypeA= ( from x in b select x.ticket_type == 1 ? 1 : 0) .Sum() ,
    TypeB= ( from x in b select x.ticket_type == 2 ? 1 : 0) .Sum() };

Upvotes: 1

etsa
etsa

Reputation: 5060

Just to show other possibility, you can try to use PIVOT operator. You have to define the name only in one place (the pivot operator). I included a 3rd ticket type for example (plus the column with the total). If you want avoid NULL and to rename fields (ticket type), you have to use COALESCE and aliases for every single field in the outer SELECT

SELECT RC2.* 
FROM
(  select user_id
    , CASE WHEN GROUPING(ticket_type)=1 THEN 'TOT_TCK' ELSE CAST(ticket_type AS VARCHAR(4)) END AS TICKET_TYPE
    , COUNT(*) AS RC 
 from  @USERS
 group by rollup(ticket_type), user_id
 ) A
 PIVOT (SUM(RC) FOR TICKET_TYPE IN ([1],[2],[3],[TOT_TCK])) AS RC2

Output:

user_id 1           2           3           TOT_TCK
------- ----------- ----------- ----------- -----------
user1   2           2           NULL        4
user2   NULL        3           NULL        3
user3   1           NULL        NULL        1

Upvotes: 1

Intern87
Intern87

Reputation: 479

In the absence of a table of data to run off, try something like this;

--CREATE TABLE #TEMP (User_ID int, type int)
--INSERT INTO #TEMP VALUES (1,1),(1,2),(1,2),(1,3),(2,2),(2,4)

SELECT User_id, type, count(User_ID) as count FROM #TEMP
group by user_ID, type

I get this;

User_id type    count
1       1       1
1       2       2
2       2       1
1       3       1
2       4       1

Upvotes: 0

Related Questions