sql statement that requires 2 distinct

Question

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)

... best way to show sample data is ddl + dml, so that we can easily test our answers before posting them. — Zohar Peled, Apr 28 '17 at 09:35
my apologies, what u mean by ddl + dml? i have created 2 tables, 1st table to show the desired output, 2nd table to show the database table. do let me know how i can show the sample data and desired output better — user1166085, Apr 28 '17 at 09:43
DDL - Data Definition Language - the create statement of your table. DML - Data Manipulation Language - the insert statements required to populate the sample table. — Zohar Peled, Apr 28 '17 at 09:44
please provide sample input and output , you are providing some html table code its confusing — Arunprasanth K V, Apr 28 '17 at 09:48
Check out my edit to your question. This is the best way to provide sample data. — Zohar Peled, Apr 28 '17 at 10:03
try this linq 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() }; — Arunprasanth K V, Apr 28 '17 at 10:15
@ArunprasanthKV would you like to submit this as an answer so i can credit you if it's the right answer? — user1166085, May 01 '17 at 08:02
@user1166085 I didn't check the query, If its solved your problem then i can make it as an answer — Arunprasanth K V, May 01 '17 at 08:35

score 1 · Answer 1 · answered Apr 28 '17 at 10:56

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

score 1 · Accepted Answer · answered May 03 '17 at 09:53

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() };

score 0 · Answer 3 · answered Apr 28 '17 at 09:41

0

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

answered Apr 28 '17 at 09:41

Intern87

469
1
6
18

thank you but this is not the desired output. i have created a html table in my qns above that shows the desired output and the database table! hope its clearer – user1166085 Apr 28 '17 at 09:44
why are you posted more than one answer ?if you want to include more then add it in your first answer itself – Arunprasanth K V May 04 '17 at 05:59

sql statement that requires 2 distinct

3 Answers3