Is it possible to use SELECT DISTINCT and GROUP BY together?

Is it possible to use SELECT DISTINCT and GROUP BY clause together? I need DISTINCT to avoid duplicate values and make a single entry of the records, and then get the Total quantity of those duplicate values.

For example, I have columns like Item Name and Quantity and their records are (product A,5), (product A,7).

Since products are the same I want it as a single entry and then total its quantity. So, the output on my report would be like this: (product A,12)

Can DISTINCT and GROUP BY clause solve this together?

Answer

Based on your comment on Used_By_Already’s answer, I think you need to use sum() over (…) construct, such as the following…

create table [dbo].[Purchases_Supplier] (
  [Purchased From] nvarchar(50),
  Address nvarchar(50),
  [Transaction Month] nvarchar(6),
  Category nvarchar(50),
  Articles nvarchar(50),
  Unit int,
  [Unit Price] money,
  Qty int,
  Tin nvarchar(50),
  Cashier nvarchar(50)
);

insert [dbo].[Purchases_Supplier] values
  ('Acme', '123 Street', '202003', 'Baubles', 'Product A', 1, 1.1, 5, 'Nickel', 'John'),
  ('Acme', '123 Street', '202003', 'Baubles', 'Product A', 1, 1.1, 7, 'Nickel', 'John'),
  ('Acme', '123 Street', '202003', 'Baubles', 'Product B', 1, 1.1, 9, 'Silver', 'Maria'),
  ('Acme', '123 Street', '202003', 'Baubles', 'Product B', 1, 1.1, 11, 'Silver', 'Maria');

declare @Supplier_Name nvarchar(50) = N'Acme',
  @Month_Purc nvarchar(6) = '202003',
  @Cat nvarchar(50) = 'Baubles';

select
  Articles,
  Qty,
  Unit,
  [Unit Price],
  [Purchased From],
  Address,
  Tin,
  Cashier,
  -- NOTE: partition by has everything in the GROUP BY except [Qty]...
  sum(Qty) over (partition by Articles, Unit, [Unit Price], [Purchased From], Address, Tin, Cashier) as Total
from dbo.Purchases_Supplier
where [Purchased From] = @Supplier_Name
and [Transaction Month] = @Month_Purc
and Category = @Cat
group by Articles, Qty, Unit, [Unit Price], [Purchased From], Address, Tin, Cashier;

Which yields the result:

Articles  Qty  Unit  Unit Price  Purchased From  Address     Tin    Cashier  Total
Product A   5     1      1.1000  Acme            123 Street  Nickel John        12
Product A   7     1      1.1000  Acme            123 Street  Nickel John        12
Product B   9     1      1.1000  Acme            123 Street  Silver Maria       20
Product B  11     1      1.1000  Acme            123 Street  Silver Maria       20