Tag: group-by

How to return all record ids which are also part of the grouping in T-SQL

I have a dataset that is laid out in a vertical format with a varying number of attributes per AppId. I currently group by the Attribute and Value to determine the maximum AppId. The scale attribute is being ignored in the Group By using “Where Attribute not in (‘Scale’)”. I would also like to know […]

Update sum of records

I have this table called ‘bill’ as shown below: Cust_id |Bill_amt |Brand |Skincare |Toner |BrandA |BrandB |A_skincare |A_toner |B_skincare |B_toner 001 |100 |A |50 |50 |NULL |NULL |NULL |NULL |NULL |NULL 001 |200 |A |150 |50 |NULL |NULL |NULL |NULL |NULL |NULL 001 |500 |B |355 |145 |NULL |NULL |NULL |NULL |NULL |NULL 009 |325 […]

How to partition data based on variable timestamp records – SQL

I have a SQL database like so: Id Text DateTime —————————- 1 Event1 1/1/1900 12:00:00 2 Event2 1/1/1900 12:10:11 3 Event3 1/1/1900 12:11:10 4 Event2 1/1/1900 12:12:12 5 Event3 1/1/1900 12:13:19 6 Event1 1/1/1900 12:14:22 7 Event2 1/1/1900 12:15:15 8 Event2 1/1/1900 12:18:16 9 Event3 1/1/1900 12:23:00 10 Event3 1/1/1900 12:24:11 I want to find […]

SQL GROUPING values from the same table

I’m having an issue with a large (100,000s record) SQL database. Here’s the basic structure: bookings – bookingcode, bookingdate payments – paycode, bookingcode, paydate, payamount pupilrecords – prcode, bookingcode, lessonscode, coursecode lessons – lessoncode, coursecode, resortcode resorts – resortcode, resortname Notes The pupil record can contain either a lessoncode or a coursecode but not both. […]

Is it possible to count per Month?

I have this table: table tblCountry name char(30) table tblSite name char(30) primary key not null, country char(30) table tblDivingClub number int name char(30) country char(30) table tblDiving diving_number int number_of_divers int diving_club int guide int And I wrote this query: select tblSite.name,tblSite.site_length,tblSite.site_depth, tblSite.water_type,tblSite.country, COUNT(distinct tblDivingClub.number) as number_of_clubs, COUNT(distinct tblDiving.diving_number) as number_of_divings, COUNT(distinct tblDiving.guide) as […]

Get the Maximum Value for each Summed amount in each Stage

I’ve searched over this and other sites, but couldn’t find the exact example to what I’m looking for. Basically, I have a Games table (sports, to be precise) and need to sum amounts by user and stage, then get the maximum for each stage. This is my table: Game_Score_User(GameId, UsrRegId, Stage, Score) — Primary key […]

How group by a query by family code

I should write a query to calculate the sum of a column where the reason code like a number. So I have this table TableX: ———————— | ReasoneCode | Duration | |————————| | 201 | 27512 | | 202 | 4372 | | 100 | 10322 | | 301.12 | 1 | | 302.06 | […]

order by number of appearances in one column and query additional columns

SELECT tbl_team.te_name, tbl_player.pl_name, tbl_scorer.sc_time FROM tbl_scorer INNER JOIN tbl_team on tbl_scorer.sID_team = tbl_team.pID_team INNER JOIN tbl_player on tbl_scorer.sID_player = tbl_player.pID_player INNER JOIN tbl_games on tbl_scorer.sID_game = tbl_games.pID_game WHERE tbl_games.pID_game = 9 this gives me something like this +——-+———-+—+ | TeamA | PlayerAA | 4 | +——-+———-+—+ | TeamB | PlayerBA | 4 | +——-+———-+—+ | […]

Grouping dates by dateadd and datediff

I’m trying to group a bunch of dates by period, namely “month” First I declare the table : –inserts 36 dates every 10 days create table sales (sales_date datetime, sales_amount decimal(12,2)) insert into sales (sales_date,sales_amount) select ‘20140101’,1000 union all select ‘20140110’,1000 union all select ‘20140120’,1000 union all select ‘20140130’,1000 union all … then I query […]

max data in one column based on another column in sql

Hello I am very new to SQL programming, started last week. I am trying to select a userID and Maxdate from a table that looks like this for example: Key USERID Date 1 111 12/1/2014 2 202 4/1/2014 3 111 3/8/2014 4 111 2/5/2014 5 202 2/10/2014 I want to make a query that would […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.