SQL with HAVING

Transact-SQL
1

Good day,
I'm a fairly newbie in SQL.
Spent quiet some time surfing the Web searching for an answer to my problem... which I think is easy to solve for an experienced programmer.
I have a database containing 4 fields in the same table (Customer, Item, Order, DateSold).
The Item field could be either (pant, shirt, blazer, shoe).
Table name: Sales

I would like to have a query that will return only the Customers that have bought >=2 pants AND >=3 shirts in the month (could be bought separately on different order).
And that, for all 2021, (Group by month).
Help please.
Thank you.

2

I think "Order" seems equivalent to "Quantity" and that you wanted that total "Order" >= 2 / 3. If not, the code will need to be adjusted.

SELECT
    Customer,
    STUFF(CONVERT(varchar(11), DATEADD(MONTH, DATEDIFF(MONTH, 0, DateSold), 0), 100), 3, 3, '') AS MonthSold,
    SUM(CASE WHEN Item = 'Pants' THEN [Order] ELSE 0 END) AS Pants,
    SUM(CASE WHEN Item = 'Shirts' THEN [Order] ELSE 0 END) AS Shirts
FROM Sales
GROUP BY 
    Customer, 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, DateSold), 0)
HAVING
    SUM(CASE WHEN Item = 'Pants' THEN [Order] ELSE 0 END) >= 2 AND
    SUM(CASE WHEN Item = 'Shirts' THEN [Order] ELSE 0 END) >= 3
ORDER BY
    Customer,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, DateSold), 0)
3

Scott,many thanks for the fast response.
Tried the code this morning and I was not able to make it work. Got an error.
Simplified the query and got rid of the date as it was not so important.
The error I'm still getting is: "Syntax-error (missing operator) in expression "SUM(CASE WHEN [TypeCompte] = "HQ" THEN [Qtee] ELSE 0 END) AS HQ"

Here is the full query

SELECT
Compte,
SUM(CASE WHEN [TypeCompte] = "HQ" THEN [Qtee] ELSE 0 END) AS HQ,
SUM(CASE WHEN [TypeCompte] = "Non-HQ" THEN [Qtee] ELSE 0 END) AS NonHQ
FROM tblComptesConsolidés
GROUP BY
Compte
HAVING
SUM(CASE WHEN [TypeCompte] = "HQ" THEN [Qtee] ELSE 0 END) >= 2 AND
SUM(CASE WHEN [TypeCompte] = "Non-HQ" THEN [Qtee] ELSE 0 END) >= 3
ORDER BY
Compte

When I close the error message, it highlight the first WHEN.
Any idea of what I'm I doing wrong?????

Note: I'm using MsAccess 365 in French (the SQL coding is done in English however) if this may help

4

Oops, I was using SQL Server T-SQL syntax, since this is intended as a SQL Server forum.

Sorry, I'm not sure of the proper syntax for MsAccess 365.

1 Like
5

Many types of SQL???
As I told you, I'm a newbe.
I will make more research.

Thanks anyway, I've learned something new out of this discussion.
Regards

Daniel

6

Yes, there are very many types of SQL. It's supposed to be a standard language, but there are many, many dialects among different dbms vendors. Kinda like there are many variations of English.