Problem with complex PostgreSQL query

Question

Problem with complex PostgreSQL query

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

Please, I need help with a query in PostgreSQL

The query is as follows:

SELECT
DISTINCT("ventas"."Product Category") AS "Categoria de Producto",
(EXTRACT(MONTH FROM "ventas"."Order Date")) AS "Mes",
"ventas"."region" AS "Región",
"ventas"."Customer Segment" AS "Segmento de Clientes",
COUNT (*) AS "Cantidad de Ventas"
FROM "ventas"
WHERE  
((EXTRACT(MONTH FROM "ventas"."Order Date"))='4') 
AND
"ventas"."region"='West'
AND
"ventas"."Customer Segment"='Corporate'
GROUP BY
"ventas"."Product Category",
"ventas"."Order Date",
"ventas"."region",
"ventas"."Customer Segment"
ORDER BY
("ventas"."Product Category");

This query returns the following result:

Categoria de Producto|Mes|Region|Segmento de Clientes|Cantidad de Ventas
Furniture            |4  |West  |Corporate           |1
Furniture            |4  |West  |Corporate           |2
Office Supplies      |4  |West  |Corporate           |1
Office Supplies      |4  |West  |Corporate           |2
Office Supplies      |4  |West  |Corporate           |4
Technology           |4  |West  |Corporate           |1

The result I'm looking for is the following:

Categoria de Producto|Mes|Region|Segmento de Clientes|Cantidad de Ventas
Furniture            |4  |West  |Corporate           |3    
Office Supplies      |4  |West  |Corporate           |7    
Technology           |4  |West  |Corporate           |1

I thought that using the keyword DISTINCT together with "Product Category" would select all product categories without duplicating rows, but that is not the result.

sql query postgresql

asked by Medina Nualart Martin 03.11.2017 в 09:52

source

2 answers

0

If I'm not wrong this subselect has to return what you need.

You treat the data obtained and add the column Cantidad de Ventas without grouping it.

SELECT
"Categoria de Producto",
"Mes",
"Región",
"Segmento de Clientes",
SUM("Cantidad de Ventas")
FROM

SELECT
DISTINCT("ventas"."Product Category") AS "Categoria de Producto",
(EXTRACT(MONTH FROM "ventas"."Order Date")) AS "Mes",
"ventas"."region" AS "Región",
"ventas"."Customer Segment" AS "Segmento de Clientes",
COUNT (*) AS "Cantidad de Ventas"
FROM "ventas"
WHERE  
((EXTRACT(MONTH FROM "ventas"."Order Date"))='4') 
AND
"ventas"."region"='West'
AND
"ventas"."Customer Segment"='Corporate'
GROUP BY
"ventas"."Product Category",
"ventas"."Order Date",
"ventas"."region",
"ventas"."Customer Segment"
ORDER BY
("ventas"."Product Category");

GROUP BY
"Categoria de Producto",
"Mes",
"Región",
"Segmento de Clientes"

answered by 03.11.2017 в 10:03

show selected file modified file file separate SQL field

score 1 · Accepted Answer

The main problem is that, in your GROUP BY , you are grouping by date instead of by month. To correct that, simply replace

"ventas"."Order Date",

... by:

EXTRACT(MONTH FROM "ventas"."Order Date"),

Also, you can remove the DISTINCT in the SELECT . It does not make sense to put a DISTINCT if you're already using a GROUP BY .

Query corrected:

SELECT
"ventas"."Product Category" AS "Categoria de Producto", -- quita el DISTINCT
(EXTRACT(MONTH FROM "ventas"."Order Date")) AS "Mes",
"ventas"."region" AS "Región",
"ventas"."Customer Segment" AS "Segmento de Clientes",
COUNT (*) AS "Cantidad de Ventas"
FROM "ventas"
WHERE  
((EXTRACT(MONTH FROM "ventas"."Order Date"))='4') 
AND
"ventas"."region"='West'
AND
"ventas"."Customer Segment"='Corporate'
GROUP BY
"ventas"."Product Category",
EXTRACT(MONTH FROM "ventas"."Order Date"), -- esta es la clave
"ventas"."region",
"ventas"."Customer Segment"
ORDER BY
("ventas"."Product Category");